Reading JOINs and Spotting Missing Rows
The boss sends another message.
"How many of our users haven't logged any expenses yet? I want a cleanup list."
The agent writes a query. Three users come back. You send the list. Two hours later, the boss writes back saying the list is wrong: she knows for a fact that six users have never logged an expense, not three.
Here is what happened. The agent used an INNER JOIN. An INNER JOIN only keeps rows where there is a match in both tables. Users with no expenses had no row in the expenses table, so they got silently dropped before the count was taken.
This is the most common JOIN bug in reporting code. By the end of this lesson, you should be able to spot it in three seconds.
- INNER JOIN: Returns only the rows where a match exists in both tables. Anything without a match disappears.
- LEFT JOIN: Returns every row from the left table. If the right table has a match, attach it. If it does not, fill the right side with NULL.
- Missing-row bug: When an INNER JOIN silently drops the rows the question was actually about.
The Picture in Words
Imagine two tables. users has Alice, Bob, and Carol. expenses has rows for Alice and Bob, but Carol has never logged anything.
| JOIN type | Result |
|---|---|
INNER JOIN | Alice, Bob. Carol disappears. |
LEFT JOIN from users | Alice, Bob, and Carol with NULL on her side. |
The question "who has never logged an expense?" is a question about Carol. An INNER JOIN cannot answer it, because Carol does not survive the join.
The Standard Pattern
The classic SQL pattern for "parents with zero children" is:
SELECT u.name
FROM users u
LEFT JOIN expenses e ON e.user_id = u.id
WHERE e.id IS NULL;
Read it slowly.
LEFT JOINkeeps every user, even those with no expenses.- For users with no expenses, the entire
e.*side of the row is NULL. WHERE e.id IS NULLkeeps only those rows where the join failed to find a match.- The result is the users who have never logged an expense.
When you see this pattern, recognise it. It is the idiom for "who has zero of X." If the agent produces it, that is a green flag. If the agent uses an INNER JOIN for the same question, that is a red flag.
PRIMM-AI+ Practice: Find the Users with Zero Expenses
Predict [AI-FREE]
The boss wants a cleanup list. The agent produces this query.
SELECT u.name
FROM users u
INNER JOIN expenses e ON e.user_id = u.id
GROUP BY u.name
HAVING COUNT(e.id) = 0;
Before running, write down:
- How many rows you think it will return.
- Whether a user with zero expenses can appear in the result.
- Whether the query, in plain English, can ever answer the boss's question.
- Your confidence score from 1 to 5.
Run
Ask the agent in Claude Code to run the query against your Neon database. You can also paste it into the Neon SQL Editor.
You should see this:
name
------
(0 rows)
Zero rows. Not three. Not six. Zero.
That tells you the query is structurally broken: it asks for users who appear in the expenses table but whose expense count is zero. That is logically impossible, because appearing in the expenses table already means at least one expense exists. The query is asking a question that can never be true.
Investigate
Write your own explanation first. The chain of reasoning is:
- The
INNER JOINdiscards every user who has no expense rows. - Only users with at least one expense survive into the grouped result.
- Asking
HAVING COUNT(e.id) = 0on a group that exists only because at least one expense matched is asking "is this number greater than zero, but also equal to zero?" - The answer is always no. Zero rows come back.
Then ask the agent these targeted questions, in order:
- "Why does the INNER JOIN drop users with zero expenses?"
- "What is the standard SQL pattern for 'parent rows with no children'?"
- "Rewrite the query using that pattern, but do not run it yet. I want to read it first."
You should see the agent return the LEFT JOIN ... IS NULL idiom from above. Read it. Confirm it matches the standard pattern. Then run it.
name
--------
Carol
Dave
Eve
Frank
Grace
Henry
(6 rows)
Six rows. The boss was right.
Modify
Change one requirement: the boss now wants the list of users with zero expenses who registered in the last 30 days. Predict how the query should change. The LEFT JOIN and the IS NULL check stay. A new condition is added to filter users by their created_at column.
Ask the agent for the change. Read the new query. Verify that the JOIN structure is intact and only the WHERE clause grew.
If the agent rewrites the JOIN, push back. The JOIN was correct.
Make [Mastery Gate]
Write a one-sentence brief for a "parents with no children" report on a different relationship in the schema: categories that have no expenses, or users with no logins, or any pair you have data for.
Ask the agent for the query. Read it. Verify it uses the LEFT JOIN ... IS NULL pattern. Run it. Approve or reject based on whether the output matches your prediction.
The gate passes when the agent produces the correct pattern and you would have caught it if the agent had used INNER JOIN by mistake.
A Second Trap: WHERE Kills LEFT JOIN
There is one more thing to watch for. A LEFT JOIN keeps unmatched rows. But if the WHERE clause filters on a column from the right table, the unmatched rows have NULL on that side, and the filter quietly drops them again.
-- Looks like a LEFT JOIN. Behaves like an INNER JOIN.
SELECT u.name
FROM users u
LEFT JOIN expenses e ON e.user_id = u.id
WHERE e.category = 'Food';
The users with no expenses have e.category = NULL, which fails the = 'Food' filter, which drops them from the result. The LEFT JOIN was decorative.
If the agent produces a query like this and the question was "users who have never bought Food," reject it. The fix is to move the right-table condition into the ON clause or to add OR e.category IS NULL to the WHERE.
When you see LEFT JOIN followed by a WHERE clause that references the right table, slow down. Ask whether the intention was to preserve the unmatched rows. Half the time, it was, and the agent's filter has silently broken it.
Try With AI
Prompt 1: Read the Pattern
Show me the standard SQL pattern for "find users with no expenses"
using a LEFT JOIN. Do not explain. After you show it, I will explain
to you why each line is necessary, and you check my explanation.
What you're learning: Inverting the teacher-student role. You explain. The agent grades. This forces you to articulate the pattern in your own words, which is when it actually lands.
Prompt 2: Spot the WHERE-Kills-LEFT-JOIN Bug
Look at this query: SELECT u.name FROM users u LEFT JOIN expenses e
ON e.user_id = u.id WHERE e.category = 'Food'. Tell me whether this
behaves like a LEFT JOIN or an INNER JOIN, and why.
What you're learning: You are practising the second-order trap. The agent will explain that the WHERE clause silently converted the join. You memorise the pattern, and from now on every LEFT JOIN ... WHERE right_table.col = ... gets a slow second look.
Prompt 3: Apply to Your Domain
In my Budget Tracker, I want to find every category that has no
expenses logged against it. Write the query using the standard
"parents with no children" pattern. Show me the query before running it.
What you're learning: You are taking the pattern off the page and applying it to your own schema. The transfer step is where reading skill turns into reviewing skill. By the end of this prompt, you should be able to produce the pattern brief and read the agent's query against it.
Checkpoint
- I can tell INNER JOIN from LEFT JOIN by reading the query.
- I can recognise the
LEFT JOIN ... IS NULLidiom for "parents with no children." - I can spot when a WHERE clause on a right-table column silently converts a LEFT JOIN into an INNER JOIN.
- I rejected at least one wrong join query and proposed the right pattern.