FULL OUTER JOIN | Engineering Interview Kit

Concept

A FULL OUTER JOIN combines the results of both a LEFT JOIN and a RIGHT JOIN.
It returns all rows from both tables.

If there is a match, it glues them together.
If a row in the Left table has no match, it is returned with NULLs on the right.
If a row in the Right table has no match, it is returned with NULLs on the left.

Mental Model

Let’s say a company merged two completely separate databases, and we want to find discrepancies between HR’s list of employees and IT’s list of computer assignments.

HR Table: Alice, Bob
IT Table: Laptop A (assigned to Alice), Laptop B (assigned to Charlie)

SELECT hr.name, it.device
FROM hr
FULL OUTER JOIN it 
  ON hr.name = it.assigned_to;

Result:

HR.Name	IT.Device	Explanation
Alice	Laptop A	Perfect Match.
Bob	NULL	Found in HR, but missing from IT.
NULL	Laptop B	Found in IT, but missing from HR (Charlie).

Trade-Offs

Pros: Excellent for Data Warehousing, ETL pipelines, and auditing tasks where you need to find discrepancies between two massive datasets.
Cons: Extremely rare in standard web application backends. It is also computationally expensive, as the database often has to perform a full scan of both tables and sort them to figure out the mismatches.

Emulating FULL OUTER JOIN in MySQL

Unlike PostgreSQL and SQL Server, MySQL does not support FULL OUTER JOIN.
If you are using MySQL, you have to manually emulate it by performing a LEFT JOIN, performing a RIGHT JOIN, and then stacking them together using a UNION (which naturally removes the duplicates).

-- MySQL Workaround
SELECT hr.name, it.device
FROM hr
LEFT JOIN it ON hr.name = it.assigned_to

UNION

SELECT hr.name, it.device
FROM hr
RIGHT JOIN it ON hr.name = it.assigned_to;

Interview Questions

Q: You want to use a FULL OUTER JOIN to find records that are explicitly misaligned (rows that exist in one table but NOT the other). How do you filter out the perfect matches?
A: You perform the FULL OUTER JOIN, and then add a WHERE clause checking for NULL on the Primary Keys of both tables using an OR condition.

SELECT hr.name, it.device
FROM hr
FULL OUTER JOIN it ON hr.name = it.assigned_to
WHERE hr.id IS NULL OR it.id IS NULL;

This query drops Alice (because she exists in both) and only returns Bob (missing IT) and Charlie (missing HR).