How to Use FULL JOIN in SQL

Use FULL JOIN when you need all rows from both tables, whether they match or not. This is ideal for audits, reconciliation, migration checks, and comparing two systems side by side.

What you’ll build or solve

You’ll learn how to use FULL JOIN in SQL to keep matched and unmatched rows from both tables. You’ll also know how to interpret NULL values on either side.

When this approach works best

This approach is the right choice when no rows from either table should be lost.

Common real-world scenarios include:

• CRM vs billing audits
• Product catalog reconciliation
• Old vs new system migration checks
• Inventory comparisons
• Employee roster merges

This is a bad idea when only one table needs guaranteed completeness. In that case, use LEFT JOIN or RIGHT JOIN.

Prerequisites

You only need:

• Two related SQL tables
• Basic join key knowledge
• Familiarity with NULL

Step-by-step instructions

Step 1: Keep all rows from both tables with FULL JOIN

Use the shared key in the join condition.

SELECT crm.email, billing.email
FROM crm_users crm
FULL JOIN billing_users billing
ON crm.user_id = billing.user_id;

This returns:

• matched users from both tables
• CRM-only users
• billing-only users

Missing values on either side appear as NULL.

This makes FULL JOIN excellent for reconciliation work.

Use COALESCE() when you need one combined display value.

SELECT COALESCE(crm.email, billing.email) AS email
FROM crm_users crm
FULL JOIN billing_users billing
ON crm.user_id = billing.user_id;

What to look for:

• Keeps every row from both tables
• Unmatched sides become NULL
• Great for audits and comparisons
• Join on shared business keys
• COALESCE() helps unify output

Examples you can copy

Inventory comparison

SELECT warehouse_a.sku, warehouse_b.sku
FROM warehouse_a
FULL JOIN warehouse_b
ON warehouse_a.sku = warehouse_b.sku;

User migration audit

SELECT legacy_users.email, new_users.email
FROM legacy_users
FULL JOIN new_users
ON legacy_users.user_id = new_users.user_id;

Team roster merge

SELECT hr.name, payroll.name
FROM hr
FULL JOIN payroll
ON hr.employee_id = payroll.employee_id;

Common mistakes and how to fix them

Mistake 1: Filtering unmatched rows away in WHERE

What the reader might do:

WHERE crm.email IS NOT NULL

Why it breaks: this removes billing-only rows.

Corrected approach:

Only filter when you intentionally want one side.

Mistake 2: Using the wrong join key

What the reader might do:

ON crm.email = billing.user_id

Why it breaks: unrelated columns create false matches.

Corrected approach:

Use the actual shared identifier.

Mistake 3: Using FULL JOIN when one table is primary

What the reader might do:

FULL JOIN profiles

Why it breaks: this adds unnecessary unmatched right-side rows.

Corrected approach:

Use LEFT JOIN when one side is primary.

Troubleshooting

If one-sided rows disappear, inspect the WHERE clause.

If too many rows appear, verify the join key.

If one combined column is needed, use COALESCE().

If your database does not support FULL JOIN, emulate it with UNION.

Quick recap

• Use FULL JOIN to keep all rows from both tables
• Unmatched sides become NULL
• Great for audits and reconciliation
• Join on shared keys
• Use COALESCE() for unified output