PostgreSQL Tutorial: FULL OUTER JOIN

August 3, 2023

Summary: in this tutorial, you will learn how to use the PostgreSQL FULL OUTER JOIN to query data from two or more tables.

Table of Contents

Introduction to the PostgreSQL FULL OUTER JOIN

Suppose that you want to perform a full outer join of two tables: A and B. The following illustrates the syntax of the FULL OUTER JOIN:

SELECT * FROM A
FULL [OUTER] JOIN B on A.id = B.id;

In this syntax, the OUTER keyword is optional.

The full outer join combines the results of both the left join and the right join.

If the rows in the joined table do not match, the full outer join sets NULL values for every column of the table that does not have the matching row.

If a row from one table matches a row in another table, the result row will contain columns populated from columns of rows from both tables.

The following Venn diagram illustrates the FULL OUTER JOIN operation:

PostgreSQL Join - Full Outer Join

The result includes the matching rows from both tables, and also the rows that do not match.

PostgreSQL FULL OUTER JOIN example

First, create two new tables for the demonstration: employees and departments:

DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS employees;

CREATE TABLE departments (
	department_id serial PRIMARY KEY,
	department_name VARCHAR (255) NOT NULL
);

CREATE TABLE employees (
	employee_id serial PRIMARY KEY,
	employee_name VARCHAR (255),
	department_id INTEGER
);

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

Second, insert some sample data into the departments and employees 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 the departments and employees tables:

SELECT * FROM departments;

PostgreSQL Full Outer Join - Departments Table

SELECT * FROM employees;

PostgreSQL Full Outer Join - Employees Table

Fourth, use the FULL OUTER JOIN to query data from both employees and departments tables.

SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id;

PostgreSQL Full Outer Join Example

The result set includes every employee who belongs to a department and every department which have an employee. In addition, it includes every employee who does not belong to a department and every department that does not have an employee.

To find the department that does not have any employees, you use a WHERE clause as follows:

SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id
WHERE
	employee_name IS NULL;

PostgreSQL Full Outer Join with WHERE clause

The result shows that the Production department does not have any employees.

To find an employee who does not belong to any department, you check for the NULL of the department_name in the WHERE clause as the following statement:

SELECT
	employee_name,
	department_name
FROM
	employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id
WHERE
	department_name IS NULL;

PostgreSQL Full Outer Join with WHERE clause example

As you see clearly from the output, Juila Mcqueen does not belong to any department.

In this tutorial, you have learned how to use the PostgreSQL FULL OUTER JOIN clause to join two or more tables.

Search

Categories

Tags