Class 37 of 58 • Relational Joins & Set Operations

SQL FULL OUTER JOIN: Syntax & Emulating in MySQL via UNION

The FULL OUTER JOIN (or FULL JOIN) produces the complete union of both tables. It returns all rows from the left table and all rows from the right table. Where rows match on the join key, columns are combined; where they do not match, NULL is inserted for whichever side is missing a match.

1. Standard ANSI Syntax (PostgreSQL / SQL Server / Oracle)

SELECT s.first_name, d.dept_name
FROM students s
FULL OUTER JOIN departments d ON s.dept_id = d.dept_id;

student_id	first_name	dept_name
1	Aman	Engineering
2	Pooja	Business
3	Rohan	Engineering
4	Vikram	NULL (unassigned student)
NULL	NULL	Arts (empty department)

2. Emulating FULL OUTER JOIN in MySQL using UNION

Because MySQL lacks a native FULL JOIN keyword, developers combine a LEFT JOIN and a RIGHT JOIN using UNION (which automatically dedupes overlapping inner-joined rows):

-- MySQL Full Outer Join Emulation:
SELECT s.first_name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id

UNION

SELECT s.first_name, d.dept_name
FROM students s
RIGHT JOIN departments d ON s.dept_id = d.dept_id;

Next, let us explore Cartesian matrices and hierarchical queries using SQL CROSS JOIN & SELF JOIN!

Frequently Asked Questions (FAQ)

1. Does MySQL support native FULL OUTER JOIN syntax?

No. MySQL does not have a native FULL JOIN keyword. However, you can achieve an identical FULL OUTER JOIN in MySQL by taking the UNION of a LEFT JOIN and a RIGHT JOIN.

2. Which major database engines support native FULL OUTER JOIN?

PostgreSQL, Oracle Database, Microsoft SQL Server, and SQLite (version 3.39+) natively support the FULL OUTER JOIN keyword.

3. What does FULL OUTER JOIN return?

It returns all records when there is a match in either table. Unmatched records from either table are padded with NULL values.