Summary: in this tutorial, you will learn how to use SQL FULL OUTER JOIN
clause to merge rows from two tables.
Introduction to SQL FULL OUTER JOIN clause #
The FULL OUTER JOIN
is an optional clause of a SELECT
statement. The FULL OUTER JOIN
clause allows you to merge rows between two tables.
Here’s the syntax of the FULL OUTER JOIN
clause:
SELECT
column1,
column2
FROM
table1
FULL OUTER JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, provide the first table (
table1
) in theFROM
clause. - Second, specify the second table (
table2
) you want to join with the first table (table1
) after theFULL OUTER JOIN
keywords. - Third, define a
condition
for matching rows between two tables. You can combine multiple conditions using theAND
andOR
operators.
The FULL OUTER JOIN
clause matches rows in the table1
with rows in the table2
.
If they match, the FULL OUTER JOIN
clause combines rows from both tables into a single row and includes it in the result set.
If a row in a table does not have a matching row in another table, the FULL OUTER JOIN
fills in NULL
for columns of the row in the missing table.
Technically, a FULL OUTER JOIN
is a combination of a LEFT JOIN
and a RIGHT JOIN
. If your database system does not support the FULL OUTER JOIN
clause but the LEFT JOIN
and RIGHT JOIN
, you can emulate the FULL OUTER JOIN
using the LEFT JOIN
and RIGHT JOIN
.
Since the OUTER
keyword is optional, you can omit it in the query like this:
SELECT
column1,
column2
FROM
table1
FULL JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)
Typically, you match rows between two tables by comparing the values of a common column using the equality operator (=
). The condition
in the FULL JOIN
clause will be like this:
SELECT
column1,
column2
FROM
table1
FULL JOIN table2 ON table2.column2 = table1.column1;
Code language: SQL (Structured Query Language) (sql)
Understanding SQL full outer join #
Suppose you have two tables:
- The
X
table has two columnsid
(key) andx
. - The
Y
table has also has two columnsid
(key) andy
.
The full outer join matches the rows between the tables X
and Y
using the values in the id
columns of both tables.
The full outer join includes rows from both tables whether or not the rows have matching rows from another table. It uses null
for columns of rows in the table that do not have matching rows in another table:
The following Venn diagram is another way to illustrate a full outer join:
SQL FULL OUTER JOIN clause example #
Suppose we have two tables employees
and departments
.
The employees
table:
employee_id | name | department_id |
---|---|---|
1 | Jane | 1 |
2 | Bob | 2 |
3 | Maria | NULL |
The departments
table:
department_id | department_name |
---|---|
1 | Sales |
2 | Marketing |
3 | HR |
The following query uses the FULL OUTER JOIN
clause to retrieve the employee ID, name, and department name from the employees
and departments
tables:
SELECT
employee_id,
name,
department_name
FROM
departments
FULL OUTER JOIN employees ON employees.department_id = departments.department_id;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Output:
employee_id | name | department_name
-------------+-------+-----------------
1 | Jane | Sales
2 | Bob | Marketing
3 | Maria | NULL
NULL | NULL | HR
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
How the query works:
The row #1 in the employees
table matches with row #1 in the departments
table, the FULL OUTER JOIN
merges them into a single row:
employee_id | name | department_id | department_id | department_name |
---|---|---|---|---|
1 | Jane | 1 | 1 | Sales |
The row #2 in the employees
table matches with row #2 in the departments
table, the FULL OUTER JOIN
merges them into a single row:
employee_id | name | department_id | department_id | department_name |
---|---|---|---|---|
2 | Bob | 2 | 2 | Marketing |
The row #3 in the employees
table does not have matching rows in the departments
table, the FULL OUTER JOIN
clause fills in NULL
for columns of the row from the departments
table:
employee_id | name | department_id | department_id | department_name |
---|---|---|---|---|
3 | Maria | NULL | NULL | NULL |
The row #3 in the departemnts
table does not have a matching row in the employees
table, the FULL OUTER JOIN
clause fills in NULL
for columns of the row from the employees
table:
employee_id | name | department_id | department_id | department_name |
---|---|---|---|---|
NULL | NULL | NULL | 3 | HR |
After matching all rows from both tables, the FULL OUTER JOIN
comes up with the following intermediate result:
employee_id | name | department_id | department_id | department_name |
---|---|---|---|---|
1 | Jane | 1 | 1 | Sales |
2 | Bob | 2 | 2 | Marketing |
3 | Maria | NULL | NULL | NULL |
NULL | NULL | NULL | 3 | HR |
The SELECT
clause retrieves the employee_id
, name
, and department_id
from the intermediate result set and returns the final result set:
employee_id | name | department_name |
---|---|---|
1 | Jane | Sales |
2 | Bob | Marketing |
3 | Maria | NULL |
NULL | NULL | HR |
Using table aliases #
To make the query more concise, you can utilize table aliases in the query that uses the FULL OUTER JOIN
clause. For example:
SELECT
employee_id,
name,
department_name
FROM
departments d
FULL OUTER JOIN employees e ON e.department_id = d.department_id;
Code language: SQL (Structured Query Language) (sql)
In this example:
First, assign table aliases:
d
to departments tablee
to employees table.
Then, use these aliases for referencing the department_id
column.
Finding non-matching rows from both tables #
The FULL OUTER JOIN
clause can help you find non-matching rows from two tables.
For example, the following query uses the FULL OUTER JOIN
clause to retrieve the rows in the employees
table that does not have matching rows in the departments
table and vice versa:
SELECT
employee_id,
name,
department_name
FROM
departments d
FULL OUTER JOIN employees e ON e.department_id = d.department_id
WHERE
department_name IS NULL
OR employee_id IS NULL;
Code language: SQL (Structured Query Language) (sql)
Output:
employee_id | name | department_name
-------------+-------+-----------------
3 | Maria | NULL
NULL | NULL | HR
Code language: SQL (Structured Query Language) (sql)
The output indicates that employee ID 3 does not belong to any department, and the HR department has no employees.
This query pattern can help examine data in the tables.
Summary #
- Use the
FULL OUTER JOIN
clause to merge rows from two tables by including rows from both tables whether or not the rows have matching rows from another table.