PostgreSQL: Full Outer Join

The FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN.

The FULL JOIN is a type of join where it returns all records from both the tables. For matching rows, the resultset will have columns populated from rows of both tables. For non-matching rows, the resultset will have NULL values for every column of the table that does not have matching data.

In some databases, it is called FULL OUTER JOIN. Here is a diagram that represents FULL OUTER JOIN.

Syntax 

SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
FULL [OUTER] JOIN <table_2>
ON <table_1.column_name> = <table_2.column_name>;

        

As per the above syntax, we have the table_1 and table_2 and they have some matching columns between them.

For the demo purpose, we will use the following Employee and Department tables.

Notice Department table is parent table with dept_id as a primary key. Employee table is child table that has dept_id as foreign key referencing dept_id column of Department table. Now let's join both tables, first table (Employee) with a second table (Department) by matching values of dept_id column.

Employee table has some employee, for e.g. emp_id = 5 who does not have dept id assigned to it and for the Department with dept_id = 4, there is no employee. Let's use FULL JOIN to select data from both tables, whether they may or may not have matching data in another table.

Syntax:

SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name
FROM Employee emp FULL JOIN department dept
ON emp.dept_id = dept.dept_id;

Here FULL JOIN selects all rows of both tables that is Employee and Department, whether they may or may not have matching data in another table.

Above for matching data, it shows all column values from Employee and Department tables as rows 1 to 6. For non-matching rows of the Department with dept_id = 4, it shows emp_id, first_name and last_name column as NULL as shown in row number 7. For non-matching rows of the Employee with emp_id = 5, it shows dept_id and dept_name column as NULL as shown in row number 8.

From the resultset, we can select any column from Employee or Department table in the SELECT clause. As you can see, in the above query we selected emp_id, first_name and last_name from the Employee table and dept_id and dept_name from the Department table.

The FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN. The FULL JOIN is a type of join where it returns all records from both the tables. For matching rows, the resultset will have columns populated from rows of both tables. For non-matching rows, the resultset will have NULL values for every column of the table that does not have matching data.