SQL FULL

What does SQL FULL JOIN do?

FULL (or FULL OUTER) JOIN returns every row from both joined tables, matching where possible and filling unmatched columns with NULL.

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: PostgreSQL, SQL Server, Oracle, Snowflake, Redshift, DB2 support FULL JOIN. MySQL and SQLite lack native support; BigQuery supports it via FULL JOIN syntax.

SQL FULL Full Explanation

FULL JOIN (often written as FULL OUTER JOIN or just FULL) is one of the ANSI-standard join types. It combines the effects of LEFT JOIN and RIGHT JOIN in a single operation. The database engine scans both input tables, produces pairs of rows that satisfy the join condition, and also retains any rows that do not find a match in the opposite table. For those non-matching rows, the columns that come from the missing side are filled with NULL values. If multiple rows satisfy the join predicate, the result contains a Cartesian combination of those rows, just like INNER or LEFT/RIGHT joins. Some vendors require the keyword OUTER while others accept FULL alone. Because NULLs appear whenever a side lacks a match, downstream filters or aggregations must be NULL-aware. FULL joins can be expensive on large tables because the engine must materialize and sort unmatched rows from both sides. Indexes on the join columns mitigate cost.

SQL FULL Syntax

SELECT <select_list>
FROM table1
FULL [OUTER] JOIN table2
     ON table1.column = table2.column;

SQL FULL Parameters

table1 (table) - First input table
table2 (table) - Second input table
ON condition (expression) - Boolean predicate that defines matching rows

Example Queries Using SQL FULL

-- Compare customers and orders, even if a customer placed no order or
-- an order exists without a customer record (data quality check)
SELECT c.customer_id,
       c.name AS customer_name,
       o.order_id,
       o.order_date
FROM   customers c
FULL JOIN orders o
       ON c.customer_id = o.customer_id;

-- Identify students or courses that have no enrollment record
SELECT s.student_id,
       c.course_id,
       e.enrolled_at
FROM   students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id
FULL OUTER JOIN courses c      ON c.course_id = e.course_id;

Expected Output Using SQL FULL

Each result set contains every customer and every order
Where a customer lacks an order, order_* columns are NULL; where an order lacks a customer, customer_* columns are NULL
Duplicate rows appear when multiple matches exist

Use Cases with SQL FULL

Auditing for orphan or unmatched rows between related tables
Merging two datasets where neither side is guaranteed to have complete coverage
Creating dimensional views that must show all dimension members and all fact rows
Data quality checks before enforcing referential integrity

Common Mistakes with SQL FULL

Assuming MySQL or SQLite support FULL JOIN natively (they do not)
Forgetting the ON clause, which turns the join into a Cartesian product followed by NULL padding
Filtering on NULL values after the join without using IS NULL/IS NOT NULL, which removes unmatched rows and defeats the purpose
Expecting FULL JOIN to be fast on large, unindexed tables

First Introduced In

SQL-92

Frequently Asked Questions

What is the performance impact of FULL JOIN?

FULL JOIN can be resource intensive because the database must collect unmatched rows from both tables. Indexes on join columns and proper statistics help, but large, unfiltered FULL JOINs may require significant memory and disk I/O.

How do I emulate FULL JOIN in MySQL?

Combine a LEFT JOIN and a RIGHT JOIN with UNION ALL, then filter duplicates:```SELECT * FROM a LEFT JOIN b ON a.id = b.idUNION ALLSELECT * FROM a RIGHT JOIN b ON a.id = b.idWHERE a.id IS NULL;```

Can I use USING instead of ON with FULL JOIN?

Yes, if both tables contain the column(s) referenced. Example:```SELECT *FROM table1FULL JOIN table2 USING (id);```

How do I identify unmatched rows after a FULL JOIN?

Check for NULLs in columns from either side. Example:```SELECT *FROM a FULL JOIN b ON a.id = b.idWHERE a.id IS NULL OR b.id IS NULL;```