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, you want to perform a full outer join of two tables: A and B. The following illustrates the syntax of the FULL OUTER JOIN:
1 2 | SELECT * FROM A FULL [OUTER] JOIN B on A.id = B.id; |
The OUTER 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 lacks a matching row. For the matching rows , a single row is included in the result set that contains columns populated from both joined tables.
The following Venn diagram illustrates the FULL OUTER JOIN operation:
The result includes the matching rows from the both tables, and also the rows that do not match.
PostgreSQL FULL OUTER JOIN example
First, we will create two new tables for the demonstration: employees and departments:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE IF NOT EXISTS departments ( department_id serial PRIMARY KEY, department_name VARCHAR (255) NOT NULL ); CREATE TABLE IF NOT EXISTS 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.
The following INSERT statements add some sample data into the departments and employees tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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); |
Next, we query data from the departments and employees tables:
1 2 3 4 5 6 7 8 9 | # SELECT * FROM departments; department_id | department_name ---------------+----------------- 1 | Sales 2 | Marketing 3 | HR 4 | IT 5 | Production (5 rows) |
1 2 3 4 5 6 7 8 9 10 | # SELECT * FROM employees; 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 | (6 rows) |
Then, we use the FULL OUTER JOIN to query data from both employees and departments tables.
1 2 3 4 5 6 | 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 doesn’t have an employee.
1 2 3 4 5 6 7 8 9 10 | 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) |
To find the department that does have any employee, we use a WHERE clause as follows:
1 2 3 4 5 6 7 8 | 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; |
1 2 3 4 | employee_name | department_name ---------------+----------------- NULL | Production (1 row) |
The result shows that the Production department does not have any employee.
To find the employee who does not belong to any department, we check for the NULL of the department_name in the WHERE clause as the following statement:
1 2 3 4 5 6 7 8 | 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; |
1 2 3 4 | employee_name | department_name ---------------+----------------- Julia Mcqueen | NULL (1 row) |
As you see, 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.