Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Joins and Subqueries

SQL FULL OUTER JOIN

Reference for SQL FULL OUTER JOIN: keep unmatched rows from both tables, compare two lists, support in SQLite 3.39+ and the MySQL workaround with UNION.

FULL OUTER JOIN (usually written FULL JOIN) returns every row from both tables. Matching rows are paired; left rows without a partner get NULL in the right columns, and right rows without a partner get NULL in the left columns.

It is the join for comparing two collections: what is in A only, in B only, and in both. Typical uses are reconciling two data sources and finding differences between a table and its backup.

SQL
SELECT a.id AS a_id, b.id AS b_id
FROM a
FULL JOIN b ON b.id = a.id;

Try it

SQL Comparing two lists

Press Run (or Ctrl + Enter) to see the result here.

SQL Only the rows that do not match on both sides

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
SELECT columns
FROM table_a
FULL [OUTER] JOIN table_b ON condition;
Part Description
FULL [OUTER] JOIN Keeps unmatched rows from both sides. OUTER is optional.
ON condition Decides which rows pair up.

Returns: matched pairs, plus left rows with NULL right columns, plus right rows with NULL left columns. Use COALESCE to show the key from whichever side has it.

Dialect differences

Dialect Notes
SQLite FULL JOIN and RIGHT JOIN from version 3.39 (2022). Older versions need the UNION workaround.
PostgreSQL, SQL Server, Oracle Supported. PostgreSQL requires merge-joinable or hash-joinable conditions.
MySQL Not supported. Emulate it with a LEFT JOIN unioned with the unmatched rows of a RIGHT JOIN.
SQL
-- MySQL workaround
SELECT a.id, b.id
FROM a LEFT JOIN b ON b.id = a.id
UNION
SELECT a.id, b.id
FROM a RIGHT JOIN b ON b.id = a.id;

Usage notes

  • Rows are never lost: every row from either table appears at least once.
  • Show the key with COALESCE, since either side's key may be NULL.
  • Differences only: add WHERE a.key IS NULL OR b.key IS NULL, as the second example does.
  • Filters in WHERE on either table can silently turn the result into a left, right or inner join; put match conditions in ON.
  • Rarely needed in everyday application queries; reconciliation and reporting are the typical uses.
  • UNION workaround: UNION removes duplicates; when rows can legitimately repeat, use UNION ALL with an anti-join on the second half.
  • Duplicates multiply as in any join when keys repeat.