SQL FULL OUTER JOIN

A FULL OUTER JOIN (often just called FULL JOIN) returns every row that a normal INNER JOIN would return, plus the unmatched rows from the left table, plus the unmatched rows from the right table. Anywhere a row has no partner on the other side, the columns from the missing table are filled in with NULL. It is the join to reach for when you need a complete picture of two tables and cannot afford to silently drop rows that don’t have a match.

Overview / How It Works

To understand FULL OUTER JOIN, it helps to compare it with the other join types. Every join starts the same way: the database engine takes the two tables named in the FROM and JOIN clauses and evaluates the ON condition for every candidate pair of rows (in practice the engine rarely brute-forces every pair — it uses indexes, hash tables, or sort-merge algorithms to find matches efficiently, but the logical result is the same as if it had).

Join type	What it keeps
INNER JOIN	Only rows that match on both sides
LEFT JOIN	All left rows, matched right rows or NULL
RIGHT JOIN	All right rows, matched left rows or NULL
FULL OUTER JOIN	All rows from both sides, matched or not

Conceptually, FULL OUTER JOIN behaves like a LEFT JOIN combined with a RIGHT JOIN, with the overlap (the rows that matched) counted only once. In fact, on databases that don’t support FULL OUTER JOIN natively (MySQL, for example, has never added it), you emulate it exactly that way: a LEFT JOIN unioned with a RIGHT JOIN (or a second LEFT JOIN with the tables swapped). SQLite added native FULL OUTER JOIN support in version 3.39 (2022), and this lesson uses that native syntax, but also shows the manual emulation because you’ll need it on databases like MySQL.

FULL OUTER JOIN is especially useful for data auditing: finding customers who never placed an order, orders that reference a customer that no longer exists, products that were never sold, or any other "show me everything, including the gaps" question.

Syntax

SELECT columns
FROM table1
FULL OUTER JOIN table2
  ON table1.key = table2.key
ORDER BY column;

Part	Meaning
FULL OUTER JOIN	Keyword pair; the word OUTER is optional — FULL JOIN means the same thing
table1 / table2	The two tables being combined; order doesn’t matter for a FULL join, unlike LEFT/RIGHT
ON table1.key = table2.key	The condition used to decide which rows are "matched"
Unmatched columns	Any column from the table that has no matching row is returned as NULL for that result row

Examples

Example 1: Staff with no department, and a department with no staff

SELECT staff.staff_name, departments.department_name
FROM staff
FULL OUTER JOIN departments
  ON staff.department_id = departments.department_id
ORDER BY departments.department_name, staff.staff_name;

Result:

staff_name	department_name
Dan	NULL
Alice	Engineering
Bob	Engineering
NULL	Marketing
Carla	Sales

Alice, Bob, and Carla all have a matching department, so they appear normally. Dan’s department_id is NULL, so he can’t match any department row — he still appears, with department_name shown as NULL. Marketing has no staff assigned to it at all, so it appears with staff_name shown as NULL. Notice the sort order: SQLite treats NULL as smaller than any real value in ascending order, so Dan’s row (NULL department) sorts before "Engineering".

Example 2: Customers without orders, and an orphaned order

SELECT customers.customer_name, orders.order_id, orders.amount
FROM customers
FULL OUTER JOIN orders
  ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_name;

Result:

customer_name	order_id	amount
NULL	502	75.5
Aiko Tanaka	NULL	NULL
John Doe	503	120.0
Maria Silva	501	250.0

Order 502 references customer_id = 4, but no customer with that id exists. A plain INNER JOIN or LEFT JOIN starting from customers would hide that row completely; the FULL OUTER JOIN surfaces it with customer_name as NULL, which is exactly the kind of referential-integrity problem this join is good at exposing. Aiko Tanaka has no orders, so she appears with NULL order columns. This is the core value of FULL OUTER JOIN: nothing on either side is silently dropped.

Example 3: Emulating FULL OUTER JOIN with UNION

Not every database supports FULL OUTER JOIN directly (MySQL is the most common example). You can build the same result with a LEFT JOIN unioned with the mirrored LEFT JOIN:

SELECT teams.team_name, players.player_name
FROM teams
LEFT JOIN players ON teams.team_id = players.team_id
UNION
SELECT teams.team_name, players.player_name
FROM players
LEFT JOIN teams ON teams.team_id = players.team_id
ORDER BY team_name;

Result:

team_name	player_name
NULL	Nina
Blue Sharks	NULL
Red Hawks	Sam

The first SELECT gets every team with its matching player (or NULL if the team has none). The second SELECT gets every player with their matching team (or NULL if the player has none). UNION removes the duplicate row that both queries produce for Sam/Red Hawks, leaving one row per team-player relationship, matched or not — the same result a native FULL OUTER JOIN produces:

SELECT teams.team_name, players.player_name
FROM teams
FULL OUTER JOIN players ON teams.team_id = players.team_id
ORDER BY teams.team_name;

Result: identical to the emulated version above — NULL/Nina, Blue Sharks/NULL, Red Hawks/Sam.

How It Works Step by Step / Under the Hood

SQL is logically processed in this order, regardless of how the query reads on the page:

1. FROM / JOIN — the tables are combined. For a FULL OUTER JOIN, the engine finds every pair of rows satisfying the ON condition, then adds back any left row that matched nothing (padded with NULLs on the right) and any right row that matched nothing (padded with NULLs on the left).
2. WHERE — filters are applied to the joined result after the outer-join padding has already happened. This is the source of the single biggest FULL OUTER JOIN mistake (see below).
3. GROUP BY / HAVING — rows are grouped and aggregate filters applied, if present.
4. SELECT — the final column list is computed.
5. ORDER BY — the result is sorted. NULL sorts first in ascending order in SQLite.
6. LIMIT — the row count is capped, if present.

The critical detail is step 1 vs. step 2: conditions inside ON only affect which rows are considered a "match" during the join itself, while conditions inside WHERE are applied afterward to the whole padded result — including rows that only exist because of the outer join.

Common Mistakes

Mistake 1: Filtering with WHERE accidentally turns FULL JOIN into INNER JOIN

SELECT customers.customer_name, orders.order_id, orders.amount
FROM customers
FULL OUTER JOIN orders
  ON customers.customer_id = orders.customer_id
WHERE orders.amount > 100;

Result: only 2 rows — John Doe/503/120.0 and Maria Silva/501/250.0.

This looks reasonable, but it silently destroys the point of the FULL OUTER JOIN. Aiko Tanaka’s row has orders.amount = NULL, and NULL > 100 evaluates to unknown (not true), so her row is thrown away by the WHERE clause — even though she was legitimately included by the join. The fix is to move any condition on the "nullable" side into the ON clause, where it only affects matching, not row inclusion:

SELECT customers.customer_name, orders.order_id, orders.amount
FROM customers
FULL OUTER JOIN orders
  ON customers.customer_id = orders.customer_id AND orders.amount > 100
ORDER BY customers.customer_name;

Result: back to all 4 rows — NULL/502/75.5, Aiko Tanaka/NULL/NULL, John Doe/503/120.0, Maria Silva/501/250.0 — because the condition now only decides whether an order counts as a "match", not whether an otherwise-unmatched row survives.

Mistake 2: Ambiguous column names after the join

SELECT customer_id, customer_name, order_id, amount
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

Result: fails with an "ambiguous column name: customer_id" error, because both customers and orders have a customer_id column and the unqualified reference doesn’t say which one to use.

Worse, even qualifying it as customers.customer_id is misleading here: after a FULL OUTER JOIN, that column is NULL for any row that came only from orders (an orphaned order). The idiomatic fix is COALESCE, which returns the first non-NULL value from either side, giving you one reliable merged key:

SELECT COALESCE(customers.customer_id, orders.customer_id) AS customer_id,
       customers.customer_name, orders.order_id, orders.amount
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customer_id;

Result: 1/Maria Silva/501/250.0, 2/John Doe/503/120.0, 3/Aiko Tanaka/NULL/NULL, 4/NULL/502/75.5 — a clean, gap-free key column no matter which side of the join a row came from.

Best Practices

• Only reach for FULL OUTER JOIN when you genuinely need unmatched rows from both sides — if you only need one side’s unmatched rows, a LEFT JOIN is simpler and usually faster.
• Always use COALESCE() to build a merged key column when you need to group, filter, or display "the" id after a FULL OUTER JOIN, since the join key can be NULL on either side.
• Put filters that should limit which rows count as a match in the ON clause; put filters that should limit the final result set in the WHERE clause — and remember WHERE runs after the outer-join padding.
• Always qualify column names with a table alias when both tables share a column name, to avoid ambiguous-column errors and NULL-related bugs.
• If your database doesn’t support FULL OUTER JOIN (e.g. MySQL), emulate it with LEFT JOIN ... UNION ... LEFT JOIN (swapping which table is on the left) as shown above.
• Use FULL OUTER JOIN as a data-quality tool: rows with a NULL key on one side often reveal orphaned foreign keys or missing reference data.

Practice Exercises

• Exercise 1: Using the employees and customers style of table from this lesson, write a FULL OUTER JOIN between two tables of your own (e.g. products and an order_items table) that shows every product, even ones that were never ordered, and every order line, even one that references a deleted product.
• Exercise 2: Take the query from Example 2 and add a WHERE clause that keeps only the rows where either side is unmatched (hint: customer_name IS NULL OR order_id IS NULL). What does this filtered result represent, and why is it useful?
• Exercise 3: Rewrite the native FULL OUTER JOIN from Example 3 as a MySQL-compatible emulation using UNION ALL instead of UNION, plus a WHERE clause on the second query to avoid duplicating matched rows. Compare the row count to the UNION version.

Summary

• FULL OUTER JOIN returns matched rows plus every unmatched row from both tables, filling missing columns with NULL.
• It is logically equivalent to a LEFT JOIN combined with a RIGHT JOIN, which is exactly how you emulate it on databases without native support.
• SQLite (3.39+), PostgreSQL, and SQL Server support FULL OUTER JOIN natively; MySQL does not.
• Conditions in WHERE run after the join and can silently discard the very unmatched rows you added the FULL JOIN to keep — put join-related conditions in ON instead.
• Use COALESCE() to build a single reliable key column, since the join key can be NULL on either side.
• FULL OUTER JOIN is a strong tool for finding orphaned foreign keys and other data-integrity gaps.