SQL JOINs

Visual Reference

INNER JOIN          LEFT JOIN           RIGHT JOIN          FULL OUTER JOIN
   A ∩ B             A (+ B if match)    B (+ A if match)    A ∪ B

INNER, LEFT, RIGHT, FULL OUTER

-- INNER — only matched rows in both tables
SELECT u.name, o.order_id FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT — all left rows, NULLs where no right match
SELECT u.name, o.order_id FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Anti-join: users with NO orders
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_id IS NULL;

-- RIGHT — rarely used, always rewritable as LEFT with tables swapped
SELECT u.name, o.order_id FROM orders o RIGHT JOIN users u ON u.id = o.user_id;

-- FULL OUTER — union of both sides
SELECT u.name, o.order_id FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;

FULL OUTER's real use: reconciliation queries — finding rows in A not in B and rows in B not in A in a single pass.

CROSS JOIN — Cartesian Product

SELECT c.color, s.size FROM colors c CROSS JOIN sizes s;   -- 4 colors x 3 sizes = 12 rows

SELF JOIN

-- Employee + manager, both from the same table — always alias
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Employees earning more than their own manager
SELECT e.name, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Multiple JOINs & Non-Equi JOINs

SELECT u.name, o.created_at, p.name AS product
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'delivered';

-- Non-equi: salary bracket matching (BETWEEN instead of =)
SELECT e.name, e.salary, b.bracket_name
FROM employees e JOIN salary_brackets b ON e.salary BETWEEN b.min_sal AND b.max_sal;

The Anti-Join Pattern — Three Ways, One NULL Trap

-- Method 1: LEFT JOIN + IS NULL — usually the fastest
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Method 2: NOT EXISTS — NULL-safe, good with a correlated subquery
SELECT u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Method 3: NOT IN — DANGEROUS if the subquery can return NULL
SELECT u.name FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);  -- must filter NULLs explicitly

Why NOT IN breaks with NULLs: x NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL — the last comparison evaluates to NULL (unknown), which makes the entire AND chain NULL, so the row is silently excluded rather than included. This is one of the most common real SQL bugs — always prefer NOT EXISTS or explicitly filter NULLs out of a NOT IN subquery.

Interview Drills

1. Why does NOT IN sometimes return zero rows when you expect matches, and NOT EXISTS doesn't have this problem? — If the NOT IN subquery's result set contains even one NULL, every comparison against that NULL evaluates to unknown, collapsing the whole AND chain to NULL/false for every row — NOT EXISTS doesn't build a value-comparison list at all, it just checks row existence, so it's immune to this.
2. When would you reach for FULL OUTER JOIN instead of two separate LEFT JOINs? — A reconciliation query needing both "in A not B" and "in B not A" in one result set — two LEFT JOINs would require a UNION of two separate queries to get the same answer.

Cross-links

[[SQL Basics & Query Fundamentals]] · [[SQL Subqueries & CTEs]] · [[SQL UNION & Set Operations]]