Introduction to the PostgreSQL FULL OUTER JOIN clause

TheFULL OUTER JOIN combine data from two tables and returns all rows from both tables, including matching and non-matching rows from both sides.

In other words, theFULL OUTER JOIN combines the results of both theleft join and theright join.

Here’s the basic syntax ofFULL OUTER JOIN clause:

SELECT select_listFROM table1FULL OUTER JOIN table2   ON table1.column_name = table2.column_name;

In this syntax:

• First, specify the columns fromtable1 andtable2 in theselect_list.
• Second, specify thetable1 that you want to retrieve data in theFROM clause.
• Third, specify thetable2 that you want to join with thetable1 in theFULL OUTER JOIN clause.
• Finally, define a condition for joining two tables.

TheFULL OUTER JOIN is also known asFULL JOIN. TheOUTER keyword is optional.

How the FULL OUTER JOIN works

Step 1. Initialize the result set:

• TheFULL OUTER JOIN starts with an empty result set.

Step 2. Match rows:

• First, identify rows intable1 andtable2 where the values in the specifiedcolumn_name match.
• Then, include these matching rows in the result set.

Step 3. Include non-matching rows from thetable1 andtable2:

• First, include rows fromtable1 that do not have a match intable2. For the columns fromtable2 in these rows, include NULLs.
• Second, include rows fromtable2 that do not have a match intable1. For the columns fromtable1 in these rows, include NULLs.

Step 4. Return the result set:

• Return the final result set will contain all rows from both tables, with matching rows and non-matching rows from bothtable1 andtable2.
• If a row has a match on both sides, combine the values into a single row.
• If there is no match on one side, the columns from the non-matching side will have NULLs.

The following Venn diagram illustrates theFULL OUTER JOIN operation:

Setting up sample tables

First,create two new tables for the demonstration:employees anddepartments:

CREATE TABLE departments (  department_idserial PRIMARY KEY,  department_nameVARCHAR (255)NOT NULL);CREATE TABLE employees (  employee_idserial PRIMARY KEY,  employee_nameVARCHAR (255),  department_idINTEGER);

Each department has zero or many employees and each employee belongs to zero or one department.

Second, insert some sample data into thedepartments andemployees tables.

INSERT INTO departments (department_name)VALUES  ('Sales'),  ('Marketing'),  ('HR'),  ('IT'),  ('Production');INSERT INTO employees (employee_name, department_id)VALUES  ('Bette Nicholson',1),  ('Christian Gable',1),  ('Joe Swank',2),  ('Fred Costner',3),  ('Sandra Kilmer',4),  ('Julia Mcqueen',NULL);

Third, query data from thedepartments andemployees tables:

SELECT * FROM departments;

Output:

department_id | department_name---------------+-----------------             1 | Sales             2 | Marketing             3 | HR             4 | IT             5 | Production(5 rows)

SELECT * FROM employees;

Output:

employee_id |  employee_name  | department_id-------------+-----------------+---------------           1 | Bette Nicholson |             1           2 | Christian Gable |             1           3 | Joe Swank       |             2           4 | Fred Costner    |             3           5 | Sandra Kilmer   |             4           6 | Julia Mcqueen   |          null(6 rows)

PostgreSQL FULL OUTER JOIN examples

Let’s take some examples of using theFULL OUTER JOIN clause.

1) Basic FULL OUTER JOIN examaple

The following query uses theFULL OUTER JOIN to query data from bothemployees anddepartments tables:

SELECT  employee_name,  department_nameFROM  employees eFULL OUTER JOIN departments d  ON d.department_id= e.department_id;

Output:

employee_name  | department_name-----------------+----------------- Bette Nicholson | Sales Christian Gable | Sales Joe Swank       | Marketing Fred Costner    | HR Sandra Kilmer   | IT Julia Mcqueen   | null null            | Production(7 rows)

The result set includes every employee who belongs to a department and every department which have an employee.

Additionally, it includes every employee who does not belong to a department and every department that does not have an employee.

2) Using FULL OUTER JOIN with WHERE clause example

The following example use theFULL OUTER JOIN with aWHERE clause to find the department that does not have any employees:

SELECT  employee_name,  department_nameFROM  employees eFULL OUTER JOIN departments d  ON d.department_id= e.department_idWHERE  employee_nameIS NULL;

Output:

employee_name | department_name---------------+----------------- null          | Production(1 row)

The result shows that theProduction department does not have any employees.

The following example use theFULL OUTER JOIN cluase with aWHERE clause to find employees who do not belong to any department:

SELECT  employee_name,  department_nameFROM  employees eFULL OUTER JOIN departments d  ON d.department_id= e.department_idWHERE  department_nameIS NULL;

Output:

employee_name | department_name---------------+----------------- Julia Mcqueen | null(1 row)

The output shows thatJuila Mcqueen does not belong to any department.

Summary

• Use the PostgreSQL FULL OUTER JOIN clause to combine data from both tables, ensuring that matching rows are included from both the left and right tables, as well as unmatched rows from either table.

Last updated onJanuary 18, 2024

On this page

• Introduction to the PostgreSQL FULL OUTER JOIN clause
• Setting up sample tables
• PostgreSQL FULL OUTER JOIN examples
• Summary