# SQL FULL OUTER JOIN: Return All Rows From Both Tables

- URL: https://betteranalyst.com/sql/full-outer-join

SQL guide · Joins

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

- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL LEFT JOIN](https://betteranalyst.com/sql/left-join)
- [SQL RIGHT JOIN](https://betteranalyst.com/sql/right-join)
- [SQL UNION ALL](https://betteranalyst.com/sql/union-all)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL UNION](https://betteranalyst.com/sql/union)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)