SQL 37 🛢️ Combining All Rows with FULL OUTER JOIN - KandZ

The FULL OUTER JOIN returns all rows from both tables, matching rows where the join condition is satisfied and filling the unmatched side with NULLs where it is not. It is the union of a LEFT JOIN and a RIGHT JOIN: every left row appears, every right row appears, and rows that match appear once with both sides populated. The result is the complete picture of two tables, including the rows on each side that have no counterpart on the other.

The FULL OUTER JOIN is the least commonly used of the outer joins, but it solves a specific problem that neither the LEFT JOIN nor the RIGHT JOIN can solve alone: finding rows that exist in one table but not the other, in both directions, in a single query. It is the tool for reconciliation, for auditing, for comparing two datasets that should match, and for reports that need to show every entity from both sides whether or not a relationship exists.

Not every database supports FULL OUTER JOIN. MySQL does not support it directly and requires a UNION of a LEFT JOIN and a RIGHT JOIN to simulate it. PostgreSQL, SQL Server, Oracle, and SQLite support it natively. This chapter covers the syntax, the behavior, the unmatched-row detection in both directions, the aggregate patterns, the MySQL workaround, and the use cases where the full outer join is the right tool.

Key point: FULL OUTER JOIN returns all rows from both tables. Matched rows appear once with both sides populated; unmatched rows appear with NULLs on the missing side. Use WHERE left.key IS NULL OR right.key IS NULL to find rows that exist in only one table. MySQL does not support FULL OUTER JOIN and requires a UNION of LEFT and RIGHT joins.

Why FULL OUTER JOIN exists

The bidirectional unmatched problem. A LEFT JOIN finds left rows with no match. A RIGHT JOIN finds right rows with no match. Neither finds both in a single query. The FULL OUTER JOIN does, which makes it the natural tool for reconciliation.

The completeness problem. A report that must show every customer and every order, whether or not they relate, needs a full outer join. A LEFT JOIN would drop orders with no customer; a RIGHT JOIN would drop customers with no order. Only the full outer join preserves both.

The symmetry problem. The full outer join is the only join that treats both tables as equally important. Every other join preserves one side and drops the unmatched rows from the other. The full outer join makes no such choice.

The comparison problem. When two datasets are supposed to match, the full outer join reveals where they do not. It is the SQL equivalent of a diff: it shows what is in A but not B, what is in B but not A, and what is in both.

The audit problem. Reconciliation reports need to show the source rows, the target rows, and the differences. The full outer join produces this in a single result set.

a. Basic FULL OUTER JOIN syntax

The syntax places the left table first, then FULL OUTER JOIN and the right table, with the matching condition in ON.

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

The FULL JOIN spelling is also valid in some databases. FULL OUTER JOIN is the standard form.

The result contains:

name	order_id	Source
Alice	101	matched
Alice	102	matched
Bob	103	matched
Carol	NULL	left only (customer with no orders)
NULL	104	right only (order with no customer)

Every customer appears, every order appears, and the matched rows appear once.

b. The shape of the result

The full outer join produces three kinds of rows:

Row type	Left columns	Right columns
Matched	Populated	Populated
Left only	Populated	NULL
Right only	NULL	Populated

A row is “left only” when the left table has a row with no match in the right table. Its right columns are NULL. A row is “right only” when the right table has a row with no match in the left table. Its left columns are NULL.

The presence of NULL does not indicate an error. It indicates that the row exists on one side of the join but not the other.

c. Finding rows that exist in only one table

The full outer join combined with a filter on the NULL keys finds rows that exist in one table but not the other.

SELECT c.customer_id, o.customer_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.customer_id IS NULL;

The result contains:

Customers with no orders (left only)
Orders with no customer (right only)

This is the reconciliation query. It shows the rows on each side that have no counterpart on the other.

To find only left-only rows:

WHERE c.customer_id IS NULL

To find only right-only rows:

WHERE o.customer_id IS NULL

To find both in a single result:

WHERE c.customer_id IS NULL OR o.customer_id IS NULL

This is the pattern that the full outer join exists for. The LEFT JOIN finds one direction; the RIGHT JOIN finds the other; the FULL OUTER JOIN finds both in one query.

d. Aggregate patterns with FULL OUTER JOIN

Aggregating over a full outer join requires care, because the unmatched rows have NULLs in the columns from one side.

SELECT
  COALESCE(c.name, 'No customer') AS customer,
  COUNT(o.order_id) AS order_count
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

The COALESCE replaces the NULL name with a label for the orders that have no customer. The COUNT(o.order_id) counts the orders; the unmatched customers have zero orders because their o.order_id is NULL.

Function	Behavior on unmatched rows
`COUNT(*)`	Counts the row
`COUNT(left.key)`	Counts only left matches
`COUNT(right.key)`	Counts only right matches
`SUM(left.col)`	NULL if no left match
`COALESCE(SUM(left.col), 0)`	Zero if no left match

The choice of which column to count depends on what the query is measuring. To count matches, count the key from the other side. To count rows on one side, count the key from that side.

e. The MySQL workaround

MySQL does not support FULL OUTER JOIN. The equivalent result is produced by a UNION of a LEFT JOIN and a RIGHT JOIN.

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

The UNION removes duplicate rows. A row that appears in both the left join and the right join — a matched row — is included only once.

The UNION ALL variant does not remove duplicates, so matched rows would appear twice. For the full outer join simulation, UNION is the correct choice.

The same pattern works in any database that lacks full outer join support, including older versions of MySQL and some other systems.

f. When to use FULL OUTER JOIN

The full outer join is appropriate in specific situations.

Situation	Use FULL OUTER JOIN
Reconciliation between two systems	Yes
Finding rows in either table with no match	Yes
Report that must show every entity from both sides	Yes
Replacing an INNER JOIN	No
Replacing a LEFT JOIN	No
Replacing a RIGHT JOIN	No
Compatibility with MySQL	Use UNION workaround

The full outer join is not a replacement for the other joins. It is a specific tool for a specific problem: showing the complete picture of two tables, including the rows that have no relationship.

The performance characteristics of a full outer join are similar to a left or right join. The optimizer may implement it as a hash join or a merge join, and the cost depends on the size of the tables and the availability of indexes on the join columns.

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total       NUMERIC(10, 2)
);

INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol');

INSERT INTO orders VALUES
(101, 1, 250.00),
(102, 1, 180.00),
(103, 2, 320.00),
(104, 9, 500.00);

-- ============================================
-- PART 2: BASIC FULL OUTER JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
-- Alice (2 orders), Bob (1 order), Carol (no order),
-- order 104 (no customer)

-- ============================================
-- PART 3: FIND ROWS IN EITHER TABLE WITH NO MATCH
-- ============================================
SELECT c.customer_id, o.customer_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.customer_id IS NULL;
-- Carol (no orders), order 104 (no customer)

-- ============================================
-- PART 4: FIND ONLY LEFT-ONLY ROWS
-- ============================================
SELECT c.customer_id, c.name
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
-- Carol

-- ============================================
-- PART 5: FIND ONLY RIGHT-ONLY ROWS
-- ============================================
SELECT o.order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- Order 104

-- ============================================
-- PART 6: AGGREGATE WITH COALESCE
-- ============================================
SELECT
  COALESCE(c.name, 'No customer') AS customer,
  COUNT(o.order_id) AS order_count
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY COALESCE(c.name, 'No customer');
-- Alice: 2, Bob: 1, Carol: 0, No customer: 1

-- ============================================
-- PART 7: MYSQL WORKAROUND
-- ============================================
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
-- Same result as FULL OUTER JOIN

-- ============================================
-- PART 8: FULL OUTER JOIN WITH ADDITIONAL FILTER
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
FULL OUTER JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.total > 200;
-- Alice: order 101; Bob: order 103; Carol: NULL;
-- NULL: order 104 (preserved)

-- ============================================
-- PART 9: RECONCILIATION REPORT
-- ============================================
SELECT
  COALESCE(c.customer_id, o.customer_id) AS id,
  c.name,
  o.order_id,
  CASE
    WHEN c.customer_id IS NULL THEN 'order only'
    WHEN o.order_id IS NULL THEN 'customer only'
    ELSE 'matched'
  END AS status
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY id;

-- ============================================
-- PART 10: COMPARING TWO DATASETS
-- ============================================
-- Source table vs target table
SELECT
  COALESCE(s.id, t.id) AS id,
  s.value AS source_value,
  t.value AS target_value,
  CASE
    WHEN s.id IS NULL THEN 'missing in source'
    WHEN t.id IS NULL THEN 'missing in target'
    WHEN s.value <> t.value THEN 'value mismatch'
    ELSE 'match'
  END AS diff
FROM source s
FULL OUTER JOIN target t ON s.id = t.id
WHERE s.id IS NULL OR t.id IS NULL OR s.value <> t.value;

These ten parts cover creating the tables, a basic full outer join, finding rows in either table with no match, finding left-only rows, finding right-only rows, aggregating with COALESCE, the MySQL workaround, a full outer join with an additional filter, a reconciliation report, and comparing two datasets.

Quick Reference

FULL OUTER JOIN Syntax

Form	Example
Explicit	`FROM a FULL OUTER JOIN b ON a.id = b.id`
Short	`FROM a FULL JOIN b ON a.id = b.id`

Row Types in the Result

Row type	Left columns	Right columns
Matched	Populated	Populated
Left only	Populated	NULL
Right only	NULL	Populated

Unmatched Detection

Pattern	Purpose
`WHERE left.key IS NULL`	Right-only rows
`WHERE right.key IS NULL`	Left-only rows
`WHERE left.key IS NULL OR right.key IS NULL`	Both directions

Database Support

Database	FULL OUTER JOIN
PostgreSQL	Native
SQL Server	Native
Oracle	Native
SQLite	Native (3.39+)
MySQL	Not supported; use UNION

MySQL Workaround

Step	Query
1	`LEFT JOIN` between the tables
2	`UNION`
3	`RIGHT JOIN` between the tables

Aggregation

Function	Behavior
`COUNT(*)`	Counts the row
`COUNT(left.key)`	Counts left matches
`COUNT(right.key)`	Counts right matches
`COALESCE(col, default)`	Replaces NULL with a label

Best Practices

✅ Do This:

-- Use FULL OUTER JOIN for reconciliation
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- Find rows in either table with no match
WHERE c.customer_id IS NULL OR o.customer_id IS NULL;

-- Use COALESCE for labels
SELECT COALESCE(c.name, 'No customer') AS customer;

-- Use the UNION workaround for MySQL
LEFT JOIN ... UNION ... RIGHT JOIN ...

❌ Don’t Do This:

-- Use FULL OUTER JOIN when LEFT JOIN suffices
-- ❌ use the minimal join for the problem

-- Forget the MySQL limitation
SELECT ... FULL OUTER JOIN ...  -- ❌ fails in MySQL

-- Use UNION ALL instead of UNION in the workaround
LEFT JOIN ... UNION ALL ... RIGHT JOIN ...  -- ❌ duplicates

-- Filter on the wrong side
WHERE c.customer_id IS NULL AND o.customer_id IS NULL  -- ❌ finds nothing

Common Pitfalls

Pitfall	Why It Happens	Fix
FULL OUTER JOIN fails	Database does not support it	Use the UNION workaround
Duplicate matched rows	Used `UNION ALL` in the workaround	Use `UNION`
Both directions not found	Used `AND` instead of `OR`	Use `WHERE a IS NULL OR b IS NULL`
Aggregate includes NULL group	Unmatched rows grouped by NULL	Use `COALESCE` for a label
Confusing result rows	Not knowing which side is NULL	Use a CASE column to label
Performance issues	Large tables, no index	Index the join columns

Real-World Examples

1. Basic Full Outer Join

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

2. Reconciliation

SELECT c.customer_id, o.customer_id
FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.customer_id IS NULL;

3. Left-Only Rows

SELECT c.name
FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

4. Right-Only Rows

SELECT o.order_id
FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

5. Aggregate with Label

SELECT COALESCE(c.name, 'No customer') AS customer, COUNT(o.order_id)
FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY COALESCE(c.name, 'No customer');

6. MySQL Workaround

SELECT c.name, o.order_id FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.name, o.order_id FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

7. Reconciliation Report

SELECT COALESCE(c.id, o.id) AS id,
  CASE WHEN c.id IS NULL THEN 'order only'
       WHEN o.id IS NULL THEN 'customer only'
       ELSE 'matched' END AS status
FROM customers c FULL OUTER JOIN orders o ON c.id = o.id;

8. Dataset Comparison

SELECT COALESCE(s.id, t.id) AS id,
  CASE WHEN s.id IS NULL THEN 'missing in source'
       WHEN t.id IS NULL THEN 'missing in target'
       WHEN s.value <> t.value THEN 'mismatch'
       ELSE 'match' END AS diff
FROM source s FULL OUTER JOIN target t ON s.id = t.id;

9. Filter in ON

SELECT c.name, o.order_id
FROM customers c FULL OUTER JOIN orders o
  ON c.customer_id = o.customer_id AND o.total > 200;

10. Three Tables

SELECT c.name, o.order_id, p.name
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
FULL OUTER JOIN products p ON o.product_id = p.product_id;

Visual

FULL OUTER JOIN Behavior

┌──────────────────────────────────────────────────────────────┐
│  customers              orders                               │
│  ┌────┬────────┐        ┌─────┬──────┐                       │
│  │ id │ name   │        │ oid │ cid  │                       │
│  ├────┼────────┤        ├─────┼──────┤                       │
│  │ 1  │ Alice  │        │ 101 │ 1    │                       │
│  │ 2  │ Bob    │        │ 102 │ 1    │                       │
│  │ 3  │ Carol  │        │ 103 │ 2    │                       │
│  └────┴────────┘        │ 104 │ 9    │                       │
│                         └─────┴──────┘                       │
│                                                              │
│  FULL OUTER JOIN ON c.id = o.cid:                            │
│  ┌────────┬─────┬─────────────┐                              │
│  │ Alice  │ 101 │ matched     │                              │
│  │ Alice  │ 102 │ matched     │                              │
│  │ Bob    │ 103 │ matched     │                              │
│  │ Carol  │ NULL│ left only   │                              │
│  │ NULL   │ 104 │ right only  │                              │
│  └────────┴─────┴─────────────┘                              │
│                                                              │
│  Both sides are preserved.                                   │
└──────────────────────────────────────────────────────────────┘

The Three Row Types

┌──────────────────────────────────────────────────────────────┐
│  MATCHED:                                                    │
│  ┌────────┬────────┐                                         │
│  │  left  │  right │                                         │
│  │  row   │  row   │                                         │
│  └────────┴────────┘                                         │
│                                                              │
│  LEFT ONLY:                                                  │
│  ┌────────┬────────┐                                         │
│  │  left  │  NULL  │                                         │
│  │  row   │        │                                         │
│  └────────┴────────┘                                         │
│                                                              │
│  RIGHT ONLY:                                                 │
│  ┌────────┬────────┐                                         │
│  │  NULL  │  right │                                         │
│  │        │  row   │                                         │
│  └────────┴────────┘                                         │
└──────────────────────────────────────────────────────────────┘

Unmatched Detection in Both Directions

┌──────────────────────────────────────────────────────────────┐
│  WHERE c.id IS NULL OR o.id IS NULL                          │
│                                                              │
│  ┌────────────────────────────────────────────────────────┐  │
│  │  c.id IS NULL  → right-only rows (orders with no customer)│
│  │  o.id IS NULL  → left-only rows (customers with no orders)│
│  └────────────────────────────────────────────────────────┘  │
│                                                              │
│  This is the reconciliation query.                           │
└──────────────────────────────────────────────────────────────┘

MySQL Workaround

┌──────────────────────────────────────────────────────────────┐
│  FULL OUTER JOIN = LEFT JOIN UNION RIGHT JOIN                │
│                                                              │
│  LEFT JOIN  → matched + left-only                            │
│  RIGHT JOIN → matched + right-only                           │
│  UNION      → matched + left-only + right-only               │
│              (duplicates removed)                            │
│                                                              │
│  UNION ALL would keep duplicates, which is wrong.            │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
FULL OUTER JOIN	All rows from both tables
Matched rows	Appear once with both sides populated
Left-only rows	Left populated, right NULL
Right-only rows	Left NULL, right populated
Unmatched detection	`WHERE left.key IS NULL OR right.key IS NULL`
Left-only detection	`WHERE right.key IS NULL`
Right-only detection	`WHERE left.key IS NULL`
MySQL support	Not supported; use UNION workaround
Workaround	`LEFT JOIN UNION RIGHT JOIN`
Aggregate with label	`COALESCE(col, 'label')`
Use case	Reconciliation, auditing, dataset comparison

Key takeaways:

FULL OUTER JOIN returns all rows from both tables. Matched rows appear once with both sides populated. Unmatched rows appear with NULLs on the missing side. It is the union of a LEFT JOIN and a RIGHT JOIN.
The result has three kinds of rows: matched, left-only, and right-only. The matched rows have both sides populated. The left-only rows have NULLs in the right columns. The right-only rows have NULLs in the left columns.
WHERE left.key IS NULL OR right.key IS NULL finds rows in either table with no match. This is the reconciliation query. It shows what is in A but not B, what is in B but not A, and what is in both.
The FULL OUTER JOIN is the tool for reconciliation and auditing. It is not a replacement for the other joins. It solves the specific problem of showing the complete picture of two tables, including the rows that have no relationship.
MySQL does not support FULL OUTER JOIN. The equivalent result is produced by a UNION of a LEFT JOIN and a RIGHT JOIN. The UNION removes duplicates; UNION ALL does not, and using it would produce duplicate matched rows.
Aggregating over a FULL OUTER JOIN requires COALESCE for labels. Unmatched rows produce NULLs, and the GROUP BY includes a NULL group unless the column is replaced with a label.
Filter placement matters. A condition in ON preserves unmatched rows. The same condition in WHERE removes them and may turn the outer join into an inner join.

Remember: The FULL OUTER JOIN is the join that preserves everything. It returns every row from both tables, matches where the join condition is satisfied, and fills the unmatched side with NULLs where it is not. It is the union of a LEFT JOIN and a RIGHT JOIN, and it is the only join that treats both tables as equally important. Its primary use is reconciliation: finding rows that exist in one table but not the other, in both directions, in a single query. It is the SQL equivalent of a diff, and it is the tool for auditing, comparison, and completeness reports. MySQL does not support it directly, and the UNION workaround produces the same result. When the question is “show me everything from both sides, whether or not it matches,” the FULL OUTER JOIN is the answer.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!