SQL FULL JOIN

What does SQL FULL JOIN do?

FULL JOIN returns all records when there is a match in either the left or right table, filling non-matching 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 JOIN: PostgreSQL, SQL Server, Oracle, Snowflake, BigQuery. Not natively in MySQL or SQLite (use UNION of LEFT and RIGHT joins instead).

SQL FULL JOIN Full Explanation

FULL JOIN (also written as FULL OUTER JOIN) combines the results of LEFT JOIN and RIGHT JOIN. It produces a result set that contains:- Every row from the left table- Every row from the right table- NULLs in place of missing columns where no match existsIf a row in the left table has no corresponding row in the right table, the right-side columns are NULL. Conversely, if a row in the right table has no match in the left table, the left-side columns are NULL.Because FULL JOIN can return many NULLs, downstream aggregations should handle NULL carefully. Performance depends on indexes on the join columns. Some database systems (e.g., MySQL) lack native FULL JOIN support and require UNION of LEFT and RIGHT joins to emulate the behavior.

SQL FULL JOIN Syntax

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

SQL FULL JOIN Parameters

- table2 (Identifier) - the second table to join
- join_condition (Boolean expression) - criteria that define matching rows (ON or USING clause)

Example Queries Using SQL FULL JOIN

-- Compare customers and orders, keep all rows
SELECT c.customer_id,
       c.name,
       o.order_id,
       o.total
FROM   customers c
FULL OUTER JOIN orders o
       ON c.customer_id = o.customer_id;

-- Show employees and contractors, regardless of overlap
SELECT e.emp_id,
       e.full_name,
       ct.contractor_id,
       ct.full_name AS contractor_name
FROM   employees e
FULL JOIN contractors ct
       USING (email);

Expected Output Using SQL FULL JOIN

Each query returns a table containing every row from both source tables
When a side has no match, the columns from the opposite table are NULL

Use Cases with SQL FULL JOIN

Creating master lists that must include every record from two sources
Data reconciliation to find mismatches between tables
Auditing orphaned rows that exist in one table but not the other

Common Mistakes with SQL FULL JOIN

Assuming FULL JOIN is supported in all systems (MySQL needs work-arounds)
Forgetting to specify an ON or USING clause, leading to cartesian products
Misinterpreting NULLs as zero or empty strings in the result

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between FULL JOIN and LEFT JOIN?

LEFT JOIN returns all rows from the left table plus matching rows from the right. FULL JOIN returns every row from both tables.

Is FULL OUTER JOIN the same as FULL JOIN?

Yes. Adding the word OUTER is optional and does not change the result.

How can I perform a FULL JOIN in MySQL?

Use LEFT JOIN UNION ALL RIGHT JOIN with a WHERE filter to remove duplicates because MySQL lacks built-in FULL JOIN.

Does FULL JOIN hurt performance?

It can on large datasets. Ensure join columns are indexed and retrieve only needed columns to mitigate performance costs.