FULL OUTER JOIN in PostgreSQL

Introduction

A FULL OUTER JOIN helps you combine rows from two tables, showing all matches and also rows that don't have a match in either table.

You want to see all customers and all orders, even if some customers have no orders and some orders have no customers.

You need to compare two lists and find which items are only in one list or the other, and which are in both.

You want to merge employee data from two departments, including employees unique to each department.

You want to find all products and their sales, including products that have never been sold and sales records without a product.

Syntax

PostgreSQL

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

FULL OUTER JOIN returns all rows from both tables.

If there is no match, columns from the other table will be NULL.

Examples

Shows all employees and all departments, matching where possible.

PostgreSQL

SELECT *
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

Lists all orders and customers, even if some orders have no customer or some customers have no orders.

PostgreSQL

SELECT orders.id, customers.name
FROM orders
FULL OUTER JOIN customers
ON orders.customer_id = customers.id;

Sample Program

This query shows all students and their scores. It also shows scores for student_id 4, who is not in the students table.

PostgreSQL

CREATE TABLE students (
  id INT,
  name TEXT
);

CREATE TABLE scores (
  student_id INT,
  score INT
);

INSERT INTO students VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO scores VALUES
(1, 85),
(3, 90),
(4, 75);

SELECT students.id, students.name, scores.score
FROM students
FULL OUTER JOIN scores
ON students.id = scores.student_id
ORDER BY students.id NULLS LAST, scores.student_id NULLS LAST;

Important Notes

FULL OUTER JOIN can return NULLs when there is no matching row in one of the tables.

Ordering results with NULLS LAST or NULLS FIRST helps keep output clear.

Use FULL OUTER JOIN when you want to keep all data from both tables, not just matches.

Summary

FULL OUTER JOIN combines all rows from two tables.

It shows matching rows and also rows without matches from either table.

Unmatched columns show NULL values.