PostgreSQL FULL OUTER JOIN

 PostgreSQL FULL OUTER JOIN

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

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 The keyword is optional.

The full outer join combines the results of both left join and 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:

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;

SELECT * FROM employees;

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;

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;

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

To find the 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;

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.