Joins

Combining tables — understanding what rows each join type keeps and drops

Why

The difference between INNER, LEFT, and FULL OUTER joins determines which rows appear in your result, and getting it wrong produces silent data loss — you get a result with no error, just missing rows. Understanding the NULL pattern that LEFT JOIN produces is essential for finding unmatched records, a very common analytics task.

Intuition

Think of a join as overlapping two tables on a common key. INNER JOIN keeps only the overlap. LEFT JOIN keeps everything from the left table and fills with NULL where there's no match on the right. FULL OUTER JOIN keeps everything from both sides, NULLs on whichever side has no match.

The NULL-filling behaviour of LEFT JOIN is actually useful — filtering WHERE right_table.id IS NULL after a LEFT JOIN is the idiomatic way to find rows that exist in the left table but have no match in the right.

Explanation

Join types

INNER JOIN

Only rows where the key matches in BOTH tables. Rows with no match on either side are dropped. The most common join — use when every row must have a match.

LEFT JOIN

All rows from the left table. Matched rows from the right. Unmatched right columns are NULL. Use when you want all left rows regardless of whether a match exists.

FULL OUTER JOIN

All rows from both tables. NULLs on whichever side has no match. Use for reconciliation — finding rows that exist in one table but not the other.

CROSS JOIN

Every row from the left combined with every row from the right. m × n rows — use deliberately for generating combinations or date scaffolding. Accidental CROSS JOINs are a common performance disaster.

Join syntax and anti-join pattern

-- INNER JOIN SELECT o.id, o.amount, c.name FROM orders o JOIN customers c ON o.customer_id = c.id; -- LEFT JOIN — keep all orders, even if customer was deleted SELECT o.id, o.amount, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id; -- Anti-join: find orders with NO matching customer SELECT o.id FROM orders o LEFT JOIN customers c ON o.customer_id = c.id WHERE c.id IS NULL; -- Self-join: compare rows in the same table SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Join on multiple conditions and non-equi joins

-- Multiple join conditions SELECT * FROM orders o JOIN promotions p ON o.customer_id = p.customer_id AND o.order_date BETWEEN p.start_date AND p.end_date; -- Non-equi join: find salary band for each employee SELECT e.name, e.salary, b.band_name FROM employees e JOIN salary_bands b ON e.salary BETWEEN b.min_salary AND b.max_salary;

Joins don't have to be on equality. Range joins and inequality joins are less common but appear in problems involving overlapping date ranges and bracket lookups.

← Previous Next →