SQL FULL OUTER JOIN

What is SQL FULL OUTER JOIN?

FULL OUTER JOIN returns all rows from both joined tables, matching rows where the join condition is true and filling non-matching columns with NULLs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FULL OUTER JOIN: PostgreSQL, SQL Server, Oracle, Snowflake, Redshift, IBM DB2. Not supported natively in MySQL or SQLite.

SQL FULL OUTER JOIN Full Explanation

FULL OUTER JOIN is a set-combining join type defined in the SQL-92 standard. It unifies the results of a LEFT JOIN and a RIGHT JOIN, producing every row from the left table and every row from the right table. When a row in one table lacks a counterpart that satisfies the ON condition in the other, the missing side’s columns are returned as NULL. Because it retains unmatched data from both tables, FULL OUTER JOIN is ideal for reconciliation, auditing, and comparative analysis. Some databases require the keyword OUTER, while others accept FULL JOIN as shorthand. Performance can degrade on very large tables if indexes on the join keys are lacking, and not all SQL engines support it natively (for example, MySQL requires a UNION workaround).

SQL FULL OUTER JOIN Syntax

SELECT column_list
FROM table1
FULL [OUTER] JOIN table2
    ON table1.key = table2.key;

SQL FULL OUTER JOIN Parameters

table1 (table) - Left input table
table2 (table) - Right input table
ON condition (boolean) - Expression that defines matching rows, typically an equality between keys.

Example Queries Using SQL FULL OUTER JOIN

-- Compare customer lists in two systems
SELECT c1.id         AS crm_id,
       c2.id         AS billing_id,
       c1.name,
       c2.name       AS billing_name
FROM   crm.customers   c1
FULL OUTER JOIN billing.customers c2
       ON c1.email = c2.email;

-- Identify dates where either store had zero sales
SELECT COALESCE(a.sales_date, b.sales_date) AS sales_date,
       a.total_sales   AS store_a_sales,
       b.total_sales   AS store_b_sales
FROM   store_a.daily_sales a
FULL JOIN store_b.daily_sales b
       ON a.sales_date = b.sales_date
ORDER BY sales_date;

Expected Output Using SQL FULL OUTER JOIN

Each query returns every distinct key from both input tables
Matching rows merge into one result row
Non-matching sides appear with NULL-filled columns

Use Cases with SQL FULL OUTER JOIN

Reconcile entities stored in two systems.
Find rows present in one table but missing in another.
Build combined timelines where either source may have gaps.
Produce full comparison reports for data migrations or merges.

Common Mistakes with SQL FULL OUTER JOIN

Assuming MySQL supports FULL OUTER JOIN natively (requires UNION of LEFT and RIGHT joins).
Forgetting to use COALESCE when selecting join keys, which may create duplicate NULL rows.
Omitting indexes on join keys, leading to slow hash or merge joins.
Expecting duplicate elimination; FULL OUTER JOIN does not implicitly perform DISTINCT.

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between FULL OUTER JOIN and INNER JOIN?

INNER JOIN returns only rows that satisfy the join condition in both tables. FULL OUTER JOIN returns those matches plus every non-matching row from both tables, padding NULLs where data is missing.

How do I filter only the non-matching rows from a FULL OUTER JOIN?

Add a WHERE clause that checks for NULLs on either side of the join key:```... FULL OUTER JOIN ...WHERE table1.key IS NULL OR table2.key IS NULL;```

Why is my FULL OUTER JOIN slow?

Large tables without indexes on the join keys force a full scan and expensive hash or sort-merge joins. Create indexes on the columns used in the ON condition or limit the dataset with predicates.

Can I use USING instead of ON?

Yes, in dialects that support the USING syntax (PostgreSQL, Oracle, Snowflake). It simplifies syntax when the join columns share the same name:```SELECT *FROM a FULL JOIN b USING (id);```