FULL OUTER JOIN

Introduction to PostgreSQL FULL OUTER JOIN

The FULL OUTER JOIN combines data from two tables and returns all rows from both sides. Matching rows are combined into a single row; unmatched rows from either table appear with NULL in the columns from the other table.

Syntax:

SELECT select_list
FROM table1
FULL OUTER JOIN table2
  ON table1.column_name = table2.column_name;

FULL JOIN is an alias — the OUTER keyword is optional.

Setting up sample tables

The examples below use an employees table and a departments table:

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
);

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);

Basic FULL OUTER JOIN example

SELECT
  employee_name,
  department_name
FROM employees e
FULL OUTER JOIN departments d
  ON d.department_id = e.department_id;

Output:

  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)

Julia Mcqueen has no department assignment, so department_name is NULL. Production has no employees assigned, so employee_name is NULL.

Finding unmatched rows with FULL OUTER JOIN and WHERE

Find departments with no employees:

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;

Output: Production with a null employee name.

Find employees not assigned to any department:

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;

Output: Julia Mcqueen with a null department name.

FULL OUTER JOIN tips

• Filter on IS NULL for a left-table column to find rows that exist only in the right table, and vice versa — this is the standard pattern for table-difference queries.
• FULL JOIN and FULL OUTER JOIN are identical; omit OUTER for brevity.
• FULL OUTER JOIN can produce large result sets — always review the row count before using results in a downstream process.
• When both tables are large, ensure the join column is indexed on both sides to avoid nested-loop performance issues.
• Use COALESCE(e.department_id, d.department_id) to get the non-NULL department ID regardless of which side is NULL.