Joins without surprises

A join that returns the wrong rows almost never raises an error. It returns a tidy table, you read off the answer, and the interviewer asks where the users with no orders went. This lesson shows which rows each join keeps, where a condition on the right table belongs, how to find rows with no match without the NOT IN trap, how to list pairs from one table once each, and how to reconcile two sources that should agree.

What INNER and LEFT joins keep

The examples come from a support team's help desk. agents has one row per agent:

tickets has one row per ticket. agent_id stays NULL until someone picks the ticket up:

An inner join (JOIN) keeps only the pairs of rows that satisfy the ON condition. A left join (LEFT JOIN) keeps every row of the left table, and where nothing on the right matches, the right table's columns come back as NULL.

SELECT a.name, t.ticket_id, t.priority
FROM agents AS a
LEFT JOIN tickets AS t ON t.agent_id = a.agent_id
ORDER BY a.agent_id, t.ticket_id;

That is 7 rows. The same query with JOIN returns 5: Chloe and Eli disappear because they have no tickets. Ticket 505 is in neither result. Its agent_id is NULL, and a comparison with NULL is never true, not even NULL = NULL, so it matches no agent; a LEFT JOIN keeps unmatched rows from the left table only.

To choose between the two, ask which table's rows must all survive. If the answer is "every agent", agents goes on the left of a LEFT JOIN. Ana and Dev also appear twice: a LEFT JOIN keeps every left row at least once, not exactly once, so the fan-out from the previous lesson still applies.

Match conditions on the right table go in ON

The ask: every agent with their urgent tickets, agents with none included. The first attempt puts the priority filter in WHERE:

SELECT a.name, t.ticket_id
FROM agents AS a
LEFT JOIN tickets AS t ON t.agent_id = a.agent_id
WHERE t.priority = 'urgent'
ORDER BY a.agent_id;

The LEFT JOIN did its job and produced the 7 rows above, Chloe and Eli included. Then WHERE ran on those rows. For Chloe and Eli, t.priority is NULL, NULL = 'urgent' is not true, and they are thrown out. Ben goes too, since his only ticket is normal. A WHERE condition like this one, which cannot be true when the right table's columns are NULL, turns the LEFT JOIN into an inner join.

The usual patch, adding OR t.priority IS NULL, does not repair it. Chloe and Eli come back, but Ben is still dropped: his row carries a normal ticket, so it is neither urgent nor NULL.

Move the condition into ON:

SELECT a.name, t.ticket_id
FROM agents AS a
LEFT JOIN tickets AS t
       ON t.agent_id = a.agent_id
      AND t.priority = 'urgent'
ORDER BY a.agent_id;

In ON, the condition decides which tickets count as a match, and every agent survives either way. A filter on the left table, such as a.team = 'billing', does belong in WHERE, because removing agents is what it is for.

Counting works the same way, with one more trap:

SELECT a.name,
       COUNT(t.ticket_id) AS urgent_tickets
FROM agents AS a
LEFT JOIN tickets AS t
       ON t.agent_id = a.agent_id
      AND t.priority = 'urgent'
GROUP BY a.agent_id, a.name
ORDER BY a.agent_id;

What the interviewer sees when you write COUNT(*) here instead: Ben, Chloe and Eli each get 1, because the row a LEFT JOIN makes for an unmatched agent is still a row. Count a column from the right table, ideally its key, and the agents with no match get 0.

"Every agent has to appear, so agents is on the left. The urgent filter decides which tickets match, so it goes in the ON clause, not WHERE. And I'll count t.ticket_id rather than star, so an agent with no urgent tickets shows 0, not 1."

Anti-joins, and why NOT IN can return nothing

An anti-join returns the rows of one table that have no match in another: here, agents with no tickets at all. There are three common ways to write it:

-- 1. LEFT JOIN, then keep the rows where nothing matched
SELECT a.name
FROM agents AS a
LEFT JOIN tickets AS t ON t.agent_id = a.agent_id
WHERE t.ticket_id IS NULL;

-- 2. NOT EXISTS
SELECT a.name
FROM agents AS a
WHERE NOT EXISTS (SELECT 1 FROM tickets AS t WHERE t.agent_id = a.agent_id);

-- 3. NOT IN (returns no rows on this data)
SELECT a.name
FROM agents AS a
WHERE a.agent_id NOT IN (SELECT agent_id FROM tickets);

The first two return Chloe and Eli. In the first, a WHERE test on the right table is exactly what you want: t.ticket_id is the right table's key, so it is NULL only on rows where no ticket matched. Testing a column that can also be NULL on a matched ticket, such as priority if it were optional, would mix the two cases.

The third returns nothing, and the cause is ticket 505. The subquery returns the values 1, 2, 4 and NULL, and x NOT IN (1, 2, 4, NULL) means x <> 1 AND x <> 2 AND x <> 4 AND x <> NULL. The last comparison is unknown for every x, so the whole condition is never true and every agent is filtered out. One NULL in the subquery empties the result, with no error. Adding WHERE agent_id IS NOT NULL inside the subquery brings Chloe and Eli back, but NOT EXISTS has no such trap, which makes it the safer habit.

An empty anti-join result is a claim ("every agent has a ticket"). Before you say it out loud, check the subquery column for NULLs.

Pairs from one table

A self-join joins a table to itself under two aliases. The team lead wants a peer-review rotation: every pair of agents on the same team, each pair listed once.

SELECT a.name AS reviewer_1, b.name AS reviewer_2, a.team
FROM agents AS a
JOIN agents AS b
  ON b.team = a.team
 AND b.agent_id > a.agent_id
ORDER BY a.agent_id, b.agent_id;

Joining on team alone returns 13 rows: 3 × 3 = 9 for billing and 2 × 2 = 4 for tech, each agent paired with themselves included. That is the fan-out rule applied to one table. b.agent_id <> a.agent_id removes the 5 self-pairs and leaves 8, with Ana-Ben and Ben-Ana as separate rows. b.agent_id > a.agent_id keeps one order of each pair, which leaves 4. A team of n people has n × (n - 1) / 2 pairs, so billing's 3 agents make 3.

Two keys, and two sources that should agree

Time is tracked in a separate tool, which exports one row per agent per ticket they worked on. Agents help on each other's tickets, so its key is (ticket_id, agent_id). time_log:

The ask: how many minutes each assigned agent logged on their own tickets. The join needs both columns of the key:

SELECT t.ticket_id, t.agent_id,
       COALESCE(l.minutes, 0) AS minutes_logged
FROM tickets AS t
LEFT JOIN time_log AS l
       ON l.ticket_id = t.ticket_id
      AND l.agent_id = t.agent_id
WHERE t.agent_id IS NOT NULL
ORDER BY t.ticket_id;

Join on ticket_id alone and ticket 501 matches both of its time rows. The result grows to 6 rows, and Ana is credited with 45 minutes on her tickets instead of 30, 15 of them Chloe's. Joined on only part of its key, time_log can return several rows per ticket. Either join on the full key, when the question is about one agent's row as here, or first aggregate time_log to the column you join on, as the reconciliation below does.

COALESCE(a, b) returns the first of its arguments that is not NULL. It belongs here because no time row means the assigned agent logged nothing: the answer is zero, not unknown. The WHERE filter is on the left table, so it removes the unassigned ticket 505 on purpose.

Finance asks a different question: do the two systems agree on which tickets exist? A full outer join keeps the unmatched rows from both sides, which is what a reconciliation needs. Bring the time log to one row per ticket first, so it matches the grain of tickets:

WITH logged AS (
    SELECT ticket_id, SUM(minutes) AS minutes
    FROM time_log
    GROUP BY ticket_id
)
SELECT COALESCE(t.ticket_id, l.ticket_id) AS ticket_id,
       CASE WHEN t.ticket_id IS NULL THEN 'time log only'
            WHEN l.ticket_id IS NULL THEN 'help desk only'
            ELSE 'both' END AS found_in,
       l.minutes
FROM tickets AS t
FULL OUTER JOIN logged AS l ON l.ticket_id = t.ticket_id
WHERE t.ticket_id IS NULL OR l.ticket_id IS NULL
ORDER BY 1;

Without the WHERE line, the result has 7 rows, one per ticket known to either system, and 3 of them are found in both. COALESCE on the key is what gives ticket 507 an id, since t.ticket_id is NULL on its row. Here, unlike the report above, minutes stays NULL on purpose: in a reconciliation the missing row is the finding, and turning it into 0 would hide it. The same NULL can mean zero in one question and missing in another, so decide which one you have before you reach for COALESCE.

MySQL has no FULL OUTER JOIN. There, the usual workaround is a LEFT JOIN, then UNION ALL with the rows of a RIGHT JOIN that found no match.

Practicing with the questions on this page

Most join questions announce the join they need in the wording:

Open each question and, before writing anything, say which table's rows must all appear in the result. For each filter, decide whether it should remove rows of that table (WHERE) or only decide what counts as a match (ON). Then run the query in the console, compare the row count with the grain you expect, and look for NULLs in every column you join on or test with IN.

The next lesson, conditional counts and rates, builds on these joins to count several conditions at once and turn them into rates.