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.
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:
The result includes the matching rows from both tables, and also the rows that do not match.
FULL OUTER JOIN example
First, create two new tables for the demonstration:
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
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
SELECT * FROM departments;
SELECT * FROM employees;
Fourth, use the
FULL OUTER JOIN to query data from both
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 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;
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.