SQL FULL OUTER JOIN

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 the FROM clause.
• Second, specify the second table (table2) you want to join with the first table (table1) after the FULL OUTER JOIN keywords.
• Third, define a condition for matching rows between two tables. You can combine multiple conditions using the AND and OR 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 columns id (key) and x.
• The Y table has also has two columns id (key) and y.

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:

The departments table:

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)

Try it

Output:

 employee_id | name  | department_name
-------------+-------+-----------------
           1 | Jane  | Sales
           2 | Bob   | Marketing
           3 | Maria | NULL
        NULL | NULL  | HRCode 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:

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:

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:

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:

After matching all rows from both tables, the FULL OUTER JOIN comes up with the following intermediate result:

The SELECT clause retrieves the employee_id, name, and department_id from the intermediate result set and returns the final result set:

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)

Try it

In this example:

First, assign table aliases:

• d to departments table
• e 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)

Try it

Output:

 employee_id | name  | department_name
-------------+-------+-----------------
           3 | Maria | NULL
        NULL | NULL  | HRCode 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.

Quiz #

Databases #

• PostgreSQL Full Join
• Oracle Full Join
• SQL Server Full Join
• SQLite Full Join
• Db2 Full Join