Full Outer Join

The full outer join or full join returns a result set that contains all rows from both the left and right tables, with the matching rows from both sides where available. If there is no match, the missing side contains null values.

The below statement illustrates the full outer join:

SELECT
    zoo_1.id,
    zoo_1.animal,
    zoo_2.id,
    zoo_2.animal
FROM
    zoo_1
FULL JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;

Output:

The Venn diagram for a FULL OUTER JOIN is below:

A PostgreSQL Join statement is used to combine data or rows from one(self-join) or more tables based on a common field between them. These common fields are generally the Primary key of the first table and Foreign key of other tables.
There are 4 basic types of joins supported by PostgreSQL, namely:

1. Inner Join
2. Left Join
3. Right Join
4. Full Outer Join

Some special PostgreSQL joins are below:

• Natural Join
• Cross Join
• Self Join

Let’s look into the 4 of the basic Joins in PostgreSQL.For the sake of this article, we will be setting up a sample database with the below commands in our psql shell:

• Create a database zoo.

  CREATE DATABASE zoo;

• Create a table zoo_1.

  CREATE TABLE zoo_1 (
      id INT PRIMARY KEY,
      animal VARCHAR (100) NOT NULL
  );

• Create a table zoo_2.

  CREATE TABLE zoo_2 (
      id INT PRIMARY KEY,
      animal VARCHAR (100) NOT NULL
  );

• Insert data into zoo_1 table.

  INSERT INTO zoo_1(id, animal)
  VALUES
      (1, 'Lion'),
      (2, 'Tiger'),
      (3, 'Wolf'),
      (4, 'Fox');

• Insert data into zoo_2 table.

  INSERT INTO zoo_2(id, animal)
  VALUES
      (1, 'Tiger'),
      (2, 'Lion'),
      (3, 'Rhino'),
      (4, 'Panther');

Now, we have two tables zoo_1 and zoo_2 with two common animals and four different animals. Let’s also assume zoo_1 is the left table.