You want to find all customers who have never placed an order. You have a `customers` table and an `orders` table joined by `customer_id`. Which query correctly solves this?
ASELECT c.name FROM customers c INNER JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL
BSELECT c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL
CSELECT c.name FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL
DSELECT c.name FROM customers c INNER JOIN orders o ON c.id = o.customer_id WHERE o.customer_id IS NULL
LEFT JOIN keeps every customer row, filling order columns with NULL where no match exists. Then WHERE o.id IS NULL isolates the customers with no matching orders. Option A (INNER JOIN) is the classic mistake: INNER JOIN only returns rows that match on both sides, so customers with no orders are excluded before the WHERE clause even runs — the result would always be empty. RIGHT JOIN (option C) would keep all orders, not all customers, which is the opposite of what we want.
Question 2 Multiple Choice
You add a filter condition `AND o.status = 'shipped'` to a LEFT JOIN query. Where you place this condition — in the ON clause versus the WHERE clause — produces different results. Which statement correctly explains why?
AON and WHERE are aliases in SQL; the query planner treats them identically for LEFT JOINs
BA condition in ON filters before rows are returned, while WHERE filters after, but the final row count is the same because SQL optimizes both paths
CA condition in ON filters during the join, so unmatched rows still appear with NULLs; a condition in WHERE filters after the join, removing those NULL rows and converting the LEFT JOIN to an INNER JOIN
DConditions belong in WHERE for correctness; ON should only contain the primary key match
This is the most important subtlety of outer joins. In `LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped'`, customers with no shipped orders still appear in the result — their order columns are NULL. But in `LEFT JOIN orders o ON c.id = o.customer_id WHERE o.status = 'shipped'`, the WHERE clause runs after the join and eliminates all rows where o.status is NULL (including unmatched customers), effectively making it an INNER JOIN. Getting this wrong silently produces incorrect results with no error message.
Question 3 True / False
A LEFT JOIN query that returns no rows from the left table proves that no rows in the left table matched the join condition.
TTrue
FFalse
Answer: False
LEFT JOIN guarantees that every row from the left table appears in the result, with NULL values for the right-side columns when there is no match. If a LEFT JOIN returns no rows at all, the problem is not the join — it must be a WHERE clause (or other filter) eliminating the unmatched rows after the join. A common bug is adding a WHERE condition on a right-side column without checking for NULL, which turns the LEFT JOIN into an effective INNER JOIN and silently discards unmatched rows.
Question 4 True / False
LEFT JOIN and RIGHT JOIN are functionally identical; you can always rewrite one as the other by swapping the order of the two tables.
TTrue
FFalse
Answer: True
This is a genuine symmetry: `A LEFT JOIN B ON condition` returns the same data as `B RIGHT JOIN A ON condition` — the table that 'keeps all rows' is just written on a different side. In practice, most SQL developers use LEFT JOIN consistently and swap table positions rather than mixing LEFT and RIGHT joins in the same query, because it makes query logic easier to follow.
Question 5 Short Answer
Explain the critical difference between placing a filter condition in the ON clause versus the WHERE clause of an outer (LEFT/RIGHT) JOIN query, and give an example of where getting this wrong would produce a silently incorrect result.
Think about your answer, then reveal below.
Model answer: A condition in ON is evaluated during the join: rows that don't satisfy it are treated as non-matching, so the left-side row still appears with NULLs for right-side columns. A condition in WHERE is evaluated after the join: it eliminates rows from the already-joined result, including rows where right-side columns are NULL (i.e., unmatched rows). Moving a filter from ON to WHERE on a LEFT JOIN silently converts it into an INNER JOIN. Example: finding customers with no 'shipped' orders — ON gets unmatched customers with NULLs; WHERE o.status = 'shipped' then drops them, returning zero results instead of the intended list.
The practical consequence is that SQL returns no error — wrong ON/WHERE placement produces results that look plausible but count the wrong thing. A query meant to list customers without shipped orders instead lists only customers WITH shipped orders, with no warning. This is one of the most common subtle bugs in SQL queries involving outer joins.