Reading SELECT and WHERE Safely
The boss replies with a follow-up.
"Pull me the top 10 expenses last month, sorted by amount. Names and totals only."
You forward the request to the agent. The agent writes a SELECT-WHERE-ORDER-BY query. The output looks fine. You almost paste it. Then you remember the five reading questions and slow down.
This lesson teaches you to read that kind of query end to end. By the time you finish, you should be able to look at any SELECT statement the agent produces and predict the row set before you run it.
- SELECT: Which columns come back.
SELECT name, amountmeans two columns.SELECT *means every column. - FROM: Which table to read.
- WHERE: Which rows to keep. The filter runs before the rows leave the database.
- ORDER BY: How to sort the kept rows.
DESCis biggest first.ASCis smallest first (the default). - LIMIT: How many rows to return.
LIMIT 10returns at most 10. - NULL: A signal that means "no value." Not zero. Not an empty string. Just "we do not know." NULL behaves differently from every other value.
A Query You Can Read
Here is the query the agent produced for the boss's request.
SELECT u.name, e.amount
FROM expenses e
JOIN users u ON u.id = e.user_id
WHERE e.date >= '2026-04-01'
AND e.date < '2026-05-01'
ORDER BY e.amount DESC
LIMIT 10;
Walk through it.
- Table: Reading from
expenses, joined tousersso we can show the name instead of the user ID. - Filter:
dateis on or after April 1, and strictly before May 1. That is the full month of April. - Sort: Largest amount first.
- Limit: At most 10 rows.
That matches the boss's English request. You can approve it without running it.
Notice the date range: >= start AND < next month start. That is the safe way to filter a month. It avoids the off-by-one error that BETWEEN introduces when the column has a timestamp (with hours and minutes) rather than just a date.
The NULL Trap
NULL is the single most common silent failure in SQL. The agent will sometimes produce a filter that looks reasonable in English and returns zero rows for the wrong reason.
-- WRONG: returns zero rows, every time
SELECT name FROM users WHERE last_login = NULL;
-- RIGHT: returns the users who have never logged in
SELECT name FROM users WHERE last_login IS NULL;
The reason: NULL means "we do not know." Asking "is unknown equal to unknown?" the database answers "we do not know," which counts as "not true," which means the row gets dropped. The only way to ask about NULL is IS NULL or IS NOT NULL.
When you read a WHERE clause and see = NULL or != NULL, reject the query. The fix is one word.
The Missing Tenant Trap
The Budget Tracker has users. In a real reporting context, a query that should be scoped to one user, one team, or one company should always have a filter that says so. Without it, the agent silently sums across everyone.
-- DANGEROUS: returns total across every user in the database
SELECT SUM(amount) FROM expenses WHERE category = 'Food';
-- SAFE: returns total for one user
SELECT SUM(amount) FROM expenses WHERE user_id = 1 AND category = 'Food';
When you read a WHERE clause on a multi-user table, the first thing you look for is the column that identifies whose data is being read. If that column is missing, you reject the query or you confirm with the boss that cross-user totals are what they actually wanted.
PRIMM-AI+ Practice: A Top-10 Report
Predict [AI-FREE]
The agent produced this query.
SELECT u.name, e.amount
FROM expenses e
JOIN users u ON u.id = e.user_id
WHERE e.date >= '2026-04-01'
AND e.date < '2026-05-01'
ORDER BY e.amount DESC
LIMIT 10;
Before running it, write down:
- How many rows you expect (a single number, not a range).
- Whether the rows will be sorted high to low or low to high.
- Whether a user with zero April expenses can appear in the result.
- Whether two users with the same amount could both appear.
- Your confidence score from 1 to 5.
Do not run the query until your prediction is on paper.
Run
In your Claude Code session, ask the agent to run this query against your Neon database. If you prefer to run it yourself, paste it into the Neon SQL Editor or into a psql shell.
You should get back something like this:
name | amount
------------+--------
Bob | 312.50
Alice | 287.45
Alice | 199.99
Bob | 145.00
Alice | 98.20
(5 rows)
Compare it to your prediction. If your row count was off, check whether April had fewer than 10 expenses in your data. If the sort was wrong, re-read DESC versus ASC. If you predicted a user that did not appear, check whether that user had any expenses at all in April.
Investigate
Write your own explanation first. Cover these in plain English:
- Why are some users repeated in the result? (More than one expense each.)
- Why does the result stop at 5 rows when
LIMITwas 10? (Only 5 expenses in April.) - What would change if
JOINwereLEFT JOIN? (Nothing here, because expenses always have a user. We will revisit this in the next lesson.)
Then ask the agent these targeted questions, in this order, only after your written explanation is done:
- "Is
DESCnecessary, or is it the default?" - "What happens if two expenses have the exact same amount?"
- "Why is the date filter written as
>= start AND < next monthinstead ofBETWEEN?"
The order matters. You explain first. You ask second. Otherwise the agent does your thinking for you and the reading skill never lands.
Modify
Change one requirement: the boss now wants the top 10 expenses under $200. Predict what the output should look like before running. Ask the agent to change exactly one clause and to leave everything else alone. Read the new query. The WHERE clause should now have an extra condition. The ORDER BY and LIMIT should be unchanged.
If the agent rewrites more than the WHERE clause, push back: "I asked you to change one clause. The ORDER BY and LIMIT were correct. Leave them alone."
This is the modify habit. One change at a time. You can read one change. You cannot read a whole rewrite.
Make [Mastery Gate]
Write a one-sentence English brief: a report you want, on the Budget Tracker, that uses SELECT, WHERE, ORDER BY, and LIMIT. Hand it to the agent. Ask for the query, not the output.
Read the query. Walk it through the five reading questions. Either approve it (and then run it) or reject it (and tell the agent which question failed).
The gate passes when you can reject at least one wrong query without running it and explain the rejection in business terms.
Try With AI
Prompt 1: Catch the NULL Bug
Write a SQL query that finds every user whose last_login is unknown
(never set). Then explain why a query using `last_login = NULL` would
return zero rows instead of the answer I asked for.
What you're learning: You are practicing the NULL trap with a concrete request. The agent will produce IS NULL. The explanation is the part you need to internalise. Once you understand why = NULL fails, you will never miss it in a code review.
Prompt 2: Tenant Filter Check
Look at this query: SELECT SUM(amount) FROM expenses WHERE category = 'Food';
Tell me what is dangerous about it as a per-user report, and rewrite
it to be safe for one specific user. Use user_id = 1 as the example.
What you're learning: You are training your eye to spot a missing tenant filter. In a real reporting context, the wrong scope is the most common reason a number is right-looking but wrong. The fix is one extra condition.
Prompt 3: Predict, Then Verify
Show me a SELECT query against the expenses table from Chapter 21 that
returns the 5 oldest expenses for user_id = 1. Do not run it. Tell me
how many rows you expect, in what order, and which user they belong to.
Then run it and compare.
What you're learning: This is the Predict-Run habit in compressed form. The agent is forced to make a claim about the output before producing it. You are practising holding the agent to its own predictions.
Checkpoint
- I can read a SELECT-WHERE-ORDER BY-LIMIT query and predict the row set before running.
- I can spot
= NULLand proposeIS NULLas the fix. - I can spot a missing tenant filter on a multi-user table.
- I rejected at least one wrong query in the Make stage without running it.