SQL FULL OUTER JOIN
Return all rows from both tables, matched where possible.
FULL OUTER JOIN returns all rows from both tables. Where there's no match, NULL values fill in the gaps.
Syntax
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.col = table2.col;
Example
-- Get all users AND all orders (matched where possible)
SELECT
u.name,
o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Imagine we have an orphan order (user_id = 99, no matching user):
-- Alice | 101
-- Alice | 102
-- Bob | 103
-- Carol | NULL <-- User with no orders
-- NULL | 104 <-- Order with no user (orphan)Run It Yourself
This example needs an order whose user does not exist. Create the tables without a foreign key so the orphan row is accepted, then add it to the same users and orders data used in the other JOIN guides.
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL); CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, total NUMERIC NOT NULL); INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@mail.com'), (2, 'Bob', 'bob@mail.com'), (3, 'Carol', 'carol@mail.com'); INSERT INTO orders (id, user_id, total) VALUES (101, 1, 150), (102, 1, 75), (103, 2, 200), (104, 99, 60); SELECT u.name, o.id AS order_id FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id ORDER BY u.id, o.id;
Output in SQLite, which sorts NULL first. PostgreSQL sorts NULL last, so its rows come back in a different order.
name order_id ----- -------- NULL 104 Alice 101 Alice 102 Bob 103 Carol NULL
Which Databases Support It
FULL OUTER JOIN works in PostgreSQL, SQL Server and SQLite 3.39.0 or later. MySQL does not support it. On MySQL or an older SQLite, combine a LEFT JOIN with the orders that have no user. This returns the same five rows:
SELECT u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id UNION SELECT u.name, o.id AS order_id FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL;
When to use: Data reconciliation, finding orphaned records, comparing two datasets.