SQL guide · Joins

SQL FULL OUTER JOIN: Return All Rows From Both Tables

A FULL OUTER JOIN (often shortened to FULL JOIN) returns every row from both tables. Where a row on one side has a match on the other, the two are combined into one output row. Where there is no match, the row is still returned and the columns belonging to the other table are filled with NULL.

Think of it as the union of a left join and a right join. It is the join to use when you need to compare two lists and see what is in the first only, what is in the second only, and what appears in both. Typical cases are reconciling a billing system against a payments system, comparing this month and last month customer lists, or checking that two copies of a table are in sync.

Because both sides are preserved, a full outer join can produce NULL in either set of columns, so you usually need COALESCE to build a single key column and IS NULL tests to classify each row. It is also less widely supported than the other joins, so check your database before relying on it.

Basic syntax

SELECT a.column_a, b.column_b
FROM table_a AS a
FULL OUTER JOIN table_b AS b
  ON a.key_column = b.key_column
[WHERE condition];

The OUTER keyword is optional, so FULL JOIN and FULL OUTER JOIN behave the same. Both tables are preserved, which means any column from either table may be NULL in the output. The ON clause decides which rows count as a match.

SQL FULL OUTER JOIN examples

Customers and orders, nothing hidden

This returns matched customer and order pairs, customers who have no orders, and orders that have no customer. It is the most complete view of how the two tables relate.

SELECT c.customer_name, o.order_id
FROM customers AS c
FULL OUTER JOIN orders AS o
  ON o.customer_id = c.customer_id;

Rows that exist in only one table

Filtering for NULL on either key keeps only the unmatched rows from each side. This removes the matched pairs, leaving the differences between the two tables.

SELECT c.customer_id AS crm_id, b.customer_id AS billing_id
FROM crm_customers AS c
FULL OUTER JOIN billing_customers AS b
  ON b.customer_id = c.customer_id
WHERE c.customer_id IS NULL
   OR b.customer_id IS NULL;

A single combined key with COALESCE

When a row has no match, one of the keys is NULL. COALESCE picks the one that exists, so you get a single month column for comparing two revenue tables.

SELECT COALESCE(a.month, b.month) AS month,
       a.revenue AS revenue_2025,
       b.revenue AS revenue_2026
FROM revenue_2025 AS a
FULL OUTER JOIN revenue_2026 AS b
  ON a.month = b.month
ORDER BY month;

Emulating a full join in MySQL

MySQL has no FULL OUTER JOIN. You can combine a left join with the unmatched rows of a right join using UNION ALL, which gives the same result as a full join.

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
UNION ALL
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Where SQL dialects differ

PostgreSQL: Supports FULL [OUTER] JOIN. The join condition must be mergejoinable or hashable, so very unusual ON expressions can raise an error that full joins are only supported with such conditions.

MySQL: Does not support FULL OUTER JOIN in any version, including 8.x. Emulate it with a LEFT JOIN and a RIGHT JOIN combined by UNION or UNION ALL as shown above.

SQL Server: Supports FULL OUTER JOIN and FULL JOIN with an ON condition. There is no USING clause.

SQLite: FULL OUTER JOIN and RIGHT JOIN were added in SQLite 3.39.0 (2022). On older versions, emulate it with LEFT JOIN plus UNION ALL.

Oracle: Supports ANSI FULL OUTER JOIN since 9i. The legacy (+) syntax cannot express a full outer join, so ANSI syntax is required.

Common mistakes

  • Running it on MySQL and getting a syntax error. Use the LEFT JOIN plus RIGHT JOIN with UNION workaround there.
  • Using UNION instead of UNION ALL in the emulation. UNION removes duplicate rows, which can wrongly collapse legitimate repeated rows.
  • Filtering in WHERE on a column from one side. Unmatched rows from the other side have NULL there and are discarded, so the join quietly becomes a left or right join.
  • Selecting only one table key. The key from the unmatched side is NULL, so wrap both keys in COALESCE to build a usable identifier.
  • Using a full join when an inner or left join is enough. Full joins can produce large results and are harder to read, so choose the narrowest join that answers the question.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator