FULL JOIN
ELI5 — The Vibe Check
FULL JOIN returns everything from both tables regardless of whether there is a match. Rows with no match on either side get NULLs. It is like LEFT JOIN and RIGHT JOIN combined. Useful for finding mismatches between two datasets.
Real Talk
FULL JOIN (or FULL OUTER JOIN) returns all rows from both tables. When a row in one table has no match in the other, the result includes it with NULL values for the missing side's columns. It is commonly used for finding discrepancies between two tables.
Show Me The Code
-- Find mismatches between two tables
SELECT a.id, b.id
FROM table_a a
FULL JOIN table_b b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;
When You'll Hear This
"FULL JOIN to find records that exist in one table but not the other." / "FULL JOIN is rarely needed in everyday app development."
Related Terms
INNER JOIN
INNER JOIN only returns rows where there is a match in BOTH tables. If a user has no orders, they do not appear in the result.
JOIN
JOIN combines rows from two tables based on a related column.
LEFT JOIN
LEFT JOIN returns all rows from the left table, and matching rows from the right table.
RIGHT JOIN
RIGHT JOIN is LEFT JOIN's mirror image — it returns all rows from the right table, and matching rows from the left.