Where SQL joins quietly go wrong in interviews

Most candidates can recite the four join types on demand. Far fewer can explain why a LEFT JOIN quietly collapsed into an inner join, or why a revenue figure doubled the moment a second table entered the query. That gap is what a sharp interviewer goes looking for, because it separates people who memorized syntax from people who have debugged a wrong number under pressure.

The join questions worth preparing for are almost never “what does INNER JOIN do.” They’re the ones where the query runs, returns rows, and the rows are wrong in a way you won’t catch unless you already know the trap.

The filter that turns a LEFT JOIN into an INNER JOIN

This is the most common join bug I have watched people commit, in interviews and in shipped code. Say you want every customer, plus their 2024 orders if they placed any. A natural first attempt:

SELECT c.name, o.id, o.created_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2024-01-01';

Run it and every customer without a 2024 order disappears. The LEFT JOIN did its job: a customer with no matching order comes back with o.created_at set to NULL. Then WHERE evaluates NULL >= '2024-01-01', which is not true, so the row gets thrown out. You have written an inner join with extra keystrokes.

The fix is knowing what the predicate is for. If it decides which rows count as a match, it belongs in ON:

SELECT c.name, o.id, o.created_at
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
  AND o.created_at >= '2024-01-01';

Every customer survives now, and the date only governs which orders attach to them. Interviewers like to move a condition between WHERE and ON and ask what changed. The clean answer: for an outer join, ON controls matching while keeping unmatched rows from the outer table, and WHERE filters the combined result afterward. Put a NULL-producing condition in WHERE and you silently drop the rows the outer join was supposed to protect.

Fan-out, or why your total is suddenly triple

Give someone an orders table and an order_items table and ask for total revenue, and a good fraction will write this:

SELECT SUM(o.total)
FROM orders o
JOIN order_items i ON i.order_id = o.id;

An order with three line items now contributes its total three times, because the join produces one row per item and each row carries the order’s full amount. The number comes back looking plausible, just inflated, which is the worst kind of wrong. This is fan-out: a one-to-many join multiplying rows on the “one” side.

The habit that saves you is thinking about the grain of the query before you aggregate. What does a single row represent after the join? If it is one row per order item, then summing an order-level column is a category error. Aggregate at the right grain instead:

SELECT SUM(o.total)
FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id);

If you genuinely need item detail alongside an order total, collapse the items in a subquery before joining so the order row stays unique. When an interviewer asks you to sum money across joined tables, saying “let me check the grain so I don’t double count” out loud is worth more than the query itself.

The NULL traps that eat anti-joins

“Find customers who have never placed an order” sounds trivial. The tempting answer hides a landmine:

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If a single row in orders has a NULL customer_id, this query returns nothing at all. Not an error, not a warning, just an empty result. NOT IN against a set that contains NULL evaluates to unknown for every candidate row, because SQL cannot prove your customer’s id is unequal to an unknown value. One dirty row poisons the whole answer.

The safe pattern is an anti-join, either with NOT EXISTS or a left join filtered on the missing side:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

This keeps every customer, then keeps only the ones where no order matched. It handles NULL customer_id values gracefully, and it tends to optimize well on indexed foreign keys. Reaching for NOT EXISTS works too; both dodge the NULL problem that NOT IN walks straight into. When you say “I would avoid NOT IN here because of NULLs,” you are telling the interviewer you have been burned before, which is the signal they want.

Self-joins, and why they read as confusing

A self-join is nothing more than a join where one table appears twice under different aliases. The classic setup is an employees table that points at itself:

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

The LEFT JOIN matters here. Use a plain inner join and the person at the top, whose manager_id is NULL, drops out of the result. Aliases do all the work: once you accept that e and m are two independent handles on the same rows, the query stops looking strange.

Self-joins also appear when you compare rows within one table. Finding pairs of employees in the same department where one out-earns another is a self-join with an inequality in the ON clause:

SELECT a.name AS higher_paid, b.name AS lower_paid, a.department
FROM employees a
JOIN employees b
  ON a.department = b.department
  AND a.salary > b.salary;

Watch the inequality. Using > rather than <> returns each pair once instead of twice, which is exactly the kind of detail interviewers notice you handling on your own.

A map of what each join actually returns

Join	Rows returned	Unmatched rows	Reach for it when
INNER	Only rows that match in both tables	Dropped from both sides	You only care about records that pair up
LEFT	All left rows, matched right rows	Left rows kept, right columns NULL	You must keep every row on the primary table
RIGHT	All right rows, matched left rows	Right rows kept, left columns NULL	Rare; usually a LEFT written backwards
FULL OUTER	Every row from both, matched where possible	Both sides kept, missing columns NULL	Reconciling two sources that each have gaps
CROSS	Every left row paired with every right row	Not applicable; it is a Cartesian product	Building grids on purpose, or an accidental bug

A self-join is not a sixth type on this table. It is any of these joins pointed at the same table twice. Cross joins rarely happen on purpose anymore, but they surface when someone writes a join with no ON clause, or needs a Cartesian product to build a date-by-region grid. Naming it is enough to show you know it exists and can tell an intentional one from a bug.

What the interviewer is actually grading

The queries above are not hard once you have seen the traps. What separates a strong answer is the running commentary around them: checking the grain before summing, calling out NULL behavior before it bites, choosing LEFT JOIN because you thought about which rows must survive. A few questions phrased the way they actually get asked:

• “You joined orders to line items and the totals look too high. What happened?”
• “Rewrite this NOT IN so a NULL in the subquery can’t break it.”
• “Give me every employee and their manager’s name, including the CEO.”
• “Why did moving that date filter into the ON clause change the row count?”

None of these reward memorization. They reward someone who can look at a result set, count the rows, and think “that is more than there should be, let me find the extra join.” That suspicion of a too-clean number is the whole skill, and you build it by writing joins against real data and refusing to trust any total until you know its grain.