SQL JOINs

Joins are the highest-frequency SQL interview topic, and the questions concentrate on three things: which join type to use, why a LEFT JOIN stopped behaving like one, and how to join a table to itself.

The types

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

ON versus WHERE

The mistake that produces a wrong answer without an error message:

-- Intended: every employee, with their 2024 orders if any.
-- Actual: an INNER JOIN. Employees with no 2024 order vanish.
SELECT e.name, o.total
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
WHERE o.order_date >= '2024-01-01';

-- Correct: the date test belongs in ON, so it filters what to MATCH,
-- not which result rows to keep.
SELECT e.name, o.total
FROM employees e
LEFT JOIN orders o
       ON o.employee_id = e.id
      AND o.order_date >= '2024-01-01';

The rule: on a LEFT JOIN, conditions on the right-hand table belong in `ON`. WHERE runs after the join, and any test against the right table's columns is UNKNOWN for the NULL-padded rows, so those rows are dropped — turning the outer join back into an inner one. (WHERE o.id IS NULL is the deliberate exception; see below.)

For an INNER JOIN the distinction does not matter, which is exactly why people carry the habit into outer joins and get burnt.

The anti-join

"Rows in A with no match in B" is one of the most common interview questions, and it has three spellings:

-- LEFT JOIN … IS NULL: match, then keep only the failures
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- NOT EXISTS: usually the clearest, and NULL-safe
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NOT IN: works, but see the NULL trap in the filtering guide
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Prefer NOT EXISTS. It is immune to the `NOT IN` NULL trap, it short-circuits on the first match, and optimisers handle it well.

Self joins

Joining a table to itself, which needs aliases to disambiguate. The classic is a manager hierarchy stored as a manager_id on the same table:

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

LEFT, not INNER — the CEO has no manager, and an inner join silently drops them. That is the detail the question is checking.

The other self-join family compares rows to other rows in the same table:

-- Employees earning more than their manager
SELECT e.first_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

-- Pairs of employees in the same department, each pair once
SELECT a.first_name, b.first_name
FROM employees a
JOIN employees b
  ON a.department_id = b.department_id
 AND a.employee_id < b.employee_id;      -- < , not <>, or every pair appears twice

a.id < b.id rather than a.id <> b.id is the detail worth noticing: it removes both the self-pairing and the mirrored duplicate in one condition.

Fan-out: the silent duplicate

Joining to a table with multiple matching rows multiplies your rows, and if you then aggregate, the numbers are wrong:

-- If an order has 3 items, the order's total is counted 3 times.
SELECT o.id, SUM(o.total)
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

Aggregate before joining, in a subquery or CTE, or aggregate the right thing — SUM(i.price * i.quantity). Whenever a join produces more rows than the left table had, ask whether that is intended.

CROSS JOIN

Every combination. Rarely what you want by accident — a missing ON in older comma-join syntax produces one — but genuinely useful for generating grids:

-- Every department paired with every month, so months with no sales
-- still appear (a "calendar spine" for reporting)
SELECT d.department_name, m.month
FROM departments d
CROSS JOIN (SELECT generate_series(1, 12) AS month) m;

Mistakes that cost the interview

• Right-table filters in `WHERE` on a `LEFT JOIN`. The most common wrong answer there

is in SQL.

• `INNER JOIN` for a self-join hierarchy, dropping the top of the tree.
• Fan-out before aggregation, inflating every sum.
• Missing join condition, producing an accidental cross join.
• Ambiguous column names. Alias every table and qualify every column.
• `NOT IN` where `NOT EXISTS` was safer.

Practise these on the SQL sheet.