# 19.3 Reading SQL: predict, then run (/building-ai-workers/business-data-and-retrieval/reading-sql) --- type: Document title: "19.3 Reading SQL: predict, then run" description: "Read SQL the way Chapter 13 reads code: predict, then run, with the traps that cause real bugs, how to read an error, and how to question the SQL a coding agent writes." status: stable order: 319.03 ksor: owner: team:panaversity audience: [ public ] approval: by: process:panaversity at: 2026-11-04T22:13:33Z chapter: "19" part: III expert_status: co-build concepts: [ "19.3" ] objectives: - { id: CCDV-F.D8.S2, label: supporting } last_verified: 2026-11-02 generated: at: 2026-11-04T22:13:33Z by: process:panaversity trust_tier: unverified build_id: sha256:e1fbcb7d9a8f8b9b7cb96875022325c02c9ba4be94cef13b301cbd39a9edad85 dirty: true ksor_version: 0.0.60 --- *About twenty minutes. Read [19.2](tables-rows-and-keys-from-zero.md) first. This concept teaches SQL the way Chapters [13](../programming-fundamentals/overview.md) and [14](../structuring-programs/overview.md) taught TypeScript and Python: you read what an agent wrote, predict what it will do, then run it and compare. You never write SQL from memory. You learn to recognize what you see.* **In everyday life.** A new bookkeeper is handed a pile of filled-in forms. Nobody expects her to design the forms. Her job is to read each one, say what it will do once it is filed, and spot the one that would do damage. That might be the transfer with no account number, or the "pay all" box ticked by mistake. Reading forms is a smaller skill than designing them, and it is the one that catches mistakes. **What you will learn.** How to read a query an agent wrote: predict what it returns, run it, and compare, with the five traps that cause real bugs. **Why it matters.** A query can look right and still change every row, or find nothing, with no error. A prediction written down before the run is how you notice. **One question, start to finish.** Luis asks the builder: "Which of our open invoices are the largest?" The builder asks a coding agent, which writes a query and shows it before running it. The builder reads it, writes down a guess ("three rows, the largest first, and the top one is over $4,000"), and only then lets it run. The guess was right. The builder now knows the query does what Luis asked, and can say why. That is the whole method of this concept, five times, with the traps that cause real bugs, and a sixth block, on reading errors, in field guide [F1](field-guide-business-data-and-retrieval.md#f1-postgres-from-zero). ### How does predict, then run work for SQL? [Chapter 13](../programming-fundamentals/overview.md) called this method **PRIMM-AI+**: Predict, Run, Investigate, Modify, Make, with the coding agent as your reading partner. For SQL it works like this: | Step | What you do | What the agent does | | --- | --- | --- | | Predict | Read the query and write down what it will return: how many rows, and roughly what is in them | Shows the query before it runs, and waits | | Run | Run it for one company, read-only as `ap_reader` unless the block says otherwise | Runs it and shows the real output | | Investigate | Compare your guess with the output. Where they differ, your picture of the query is wrong somewhere | Answers "explain line 3" or "why did this row come back?" | | Modify | Change one thing, a condition or a column, and predict again | Makes the change you ask for, and nothing else | | Make | In this book, the agent writes the SQL. Your "make" is the plain-words request it starts from | Writes the query from your request | Every output below was produced by running the block against the lab's data, with Cedar Ridge as the company. The blocks are in the lab download, in `concept-blocks/sql/`, and [Lab 19](lab.md), Task 4b, runs each one with you, after the data is loaded. Every block is always undone at the end, so nothing in the database changes. **Write your prediction down before you read the output.** The block does not teach you. Your wrong prediction does. ### Which four words do the blocks use? The lab's real tables hold several companies' rows side by side, so every row has a **`tenant_id`** column saying whose it is, such as `org_cedar` for Cedar Ridge Supply. Each block runs with one company **set**: the database is told "this request is for Cedar Ridge". Then it shows only Cedar Ridge's rows ([19.8](row-level-security.md) and [19.9](one-company-per-transaction.md) explain how). Each block runs as a **role**, a database login with its own rights: `ap_reader` may only read, and `ap_loader`, the loader, may also change rows ([19.8](row-level-security.md)). And each block runs inside one **transaction**, a group of statements that is kept or undone together ([19.9](one-company-per-transaction.md)). Here it is always undone. The runner also sets a marker (a savepoint) before each block, so a block that fails is undone back to its marker, and the next block still runs. SQL does not care about capital letters in its own words, so `COUNT` and `count` are the same. ### Block 1. Filter, sort, take the first few ```sql SELECT invoice_number, vendor_id, amount_cents FROM invoices WHERE tenant_id = 'org_cedar' AND status = 'open' ORDER BY amount_cents DESC LIMIT 3; SELECT '1290000' < '61875' AS compared_as_text, 1290000 < 61875 AS compared_as_numbers; ``` *Predict:* how many rows does the first query return, and in what order? In the second, which comparison is true? (A `SELECT` can compute a value without reading any table, so it has no `FROM`.) ```text invoice_number | vendor_id | amount_cents ---------------+-----------+------------- INV-6201 | V001 | 412075 INV-6210 | V001 | 325000 INV-6204 | V004 | 268800 (3 rows) compared_as_text | compared_as_numbers -----------------+-------------------- true | false ``` In plain words: keep Cedar Ridge's open invoices, sort them by amount, largest first (`DESC` means descending, from high to low), and keep the first three. `AS` gives a column in the answer a name of your choice. The second query is Chapter 13's past-due report again, the one that sorted invoice amounts as text and put a $12,900.00 invoice near the bottom. As text, "1290000" comes before "61875", because text is compared one character at a time, and "1" comes before "6". As numbers, 1,290,000 is the larger. That is why the AP tables keep money in a number column, `amount_cents bigint`, never in text ([19.4](designing-the-ap-tables.md)). ![Block 1's query written in a box, one clause per line: SELECT invoice_number, vendor_id, amount_cents, FROM invoices, WHERE tenant_id is org_cedar and status is open, ORDER BY amount_cents DESC, LIMIT 3. Beside each line, a numbered circle giving the order the database applies it: FROM 1, in red-orange, WHERE 2, SELECT 3, ORDER BY 4, LIMIT 5. Beside each, what it does: start from this table, keep only the rows that pass the test, keep only these columns, sort them largest first, keep the first three. Below: written order SELECT, FROM, WHERE, ORDER BY, LIMIT, and applied order FROM, WHERE, SELECT, ORDER BY, LIMIT. A note: read a query in the applied order, which table, which rows, which columns, in what order, how many.](img/chapter-19-query-anatomy.png) *Figure 19.6. A query is written starting with SELECT, but the database starts with FROM. Read it in the order it is applied.* ### Block 2. A join gives one row for every match ```sql SELECT count(*) AS vendors FROM vendors WHERE tenant_id = 'org_cedar'; SELECT count(*) AS rows_after_join FROM vendors v JOIN invoices i ON i.tenant_id = v.tenant_id AND i.vendor_id = v.vendor_id WHERE v.tenant_id = 'org_cedar'; ``` *Predict:* Cedar Ridge has 9 vendors and 20 invoices. How many rows does the join return? ```text vendors ------- 9 rows_after_join --------------- 20 ``` In plain words: a join ([19.2](tables-rows-and-keys-from-zero.md)) gives one row for every pair that matches. So a vendor with three invoices appears three times, and a vendor with none does not appear at all. Cedar Ridge has 20 invoices, so the join has 20 rows. `v` and `i` are short names for the two tables, given in the `FROM` and `JOIN` lines. The trap: a report that "counts vendors" after a join is really counting invoices. When an agent's answer is bigger than you expected, ask it: "Which join multiplied the rows?" ![Three columns. Left, three of Cedar Ridge's nine vendors: V001 Treasure Valley Lumber, V002 Snake River Fasteners, V103 Idaho Power, and a dashed, grayed V999, an imagined vendor with no invoices, labeled no match, so it would not appear at all. Middle, six invoices, each with its vendor id, and lines from each vendor to its invoices: three from V001, two from V002, one from V103. Right, the result, one row per match: Treasure Valley Lumber three times, with INV-6201, INV-6210 and INV-6218, then Snake River Fasteners twice and Idaho Power once. A note: V001 appears 3 times, once per invoice. Below: Cedar Ridge's 9 vendors and 20 invoices give 20 rows, and counting after a join counts matches, not vendors.](img/chapter-19-join-rows.png) *Figure 19.7. A join gives one row for every match, so 9 vendors and 20 invoices give 20 rows.* ### Block 3. Count rows, or count values ```sql SELECT count(*) AS invoices, count(due_date) AS with_a_due_date FROM invoices WHERE tenant_id = 'org_cedar'; SELECT status, count(*) AS invoices, sum(amount_cents) AS total_cents FROM invoices WHERE tenant_id = 'org_cedar' GROUP BY status ORDER BY status; ``` *Predict:* will the two counts in the first query be the same? How many rows does the second return? ```text invoices | with_a_due_date ---------+---------------- 20 | 18 status | invoices | total_cents -------+----------+------------ open | 16 | 2132900 paid | 4 | 357700 ``` In plain words: `count(*)` counts rows. `count(due_date)` counts only the rows where `due_date` has a value, and two of Cedar Ridge's invoices have none yet. `GROUP BY status` puts the rows in piles, one per status, and `sum` adds up each pile. Two numbers that should be equal and are not are often the first sign of missing data. ### Block 4. `NULL` means "no value", and nothing equals it ```sql SELECT po_number FROM purchase_orders WHERE tenant_id = 'org_cedar' AND invoice_number = NULL; SELECT po_number FROM purchase_orders WHERE tenant_id = 'org_cedar' AND invoice_number IS NULL; ``` *Predict:* `purchase_orders` holds each purchase order, and its `invoice_number` names the invoice it matched, if any. The two queries differ by one word. Do they return the same rows? ```text po_number --------- (0 rows) po_number --------- PO-3108 (1 row) ``` In plain words: **`NULL`** is how a database writes "no value here", like a blank cell. A purchase order with no invoice yet has `NULL` in `invoice_number`. A comparison with `NULL` is never true, not even `NULL = NULL`, so the first query finds nothing, with no error. The right test is `IS NULL`, or `IS NOT NULL`. If an agent's query uses `= NULL`, it is wrong, every time. ![Two halves. Left, GROUP BY status: Cedar Ridge's 20 invoices drawn as slips in two piles, 16 open and 4 paid, with count of 16 and a sum of $21,329.00 under open, and a count of 4 and a sum of $3,577.00 under paid, one row per pile in the answer. Right, NULL, no value here: four invoices with their due dates, two of them blank and marked NULL, INV-6210 and INV-6219. Below, four lines with their results: count(*) gives 20, counts rows. count(due_date) gives 18, skips the 2 blanks. due_date = NULL gives 0 rows, nothing equals NULL. due_date IS NULL gives 2 rows, the right test.](img/chapter-19-piles-and-blanks.png) *Figure 19.8. GROUP BY makes one pile per value. NULL is a blank that counts skip and = never matches.* ### Block 5. The most dangerous line an agent can write This block runs as the loader, the role that may change rows, and is undone at the end. ```sql SELECT count(*) AS rows_it_would_touch FROM vendors WHERE tenant_id = 'org_cedar' AND vendor_id = 'V003'; UPDATE vendors SET terms = 'Net 45' WHERE tenant_id = 'org_cedar' AND vendor_id = 'V003'; UPDATE vendors SET terms = 'Net 45'; ``` *Predict:* how many rows does each `UPDATE` change? ```text rows_it_would_touch ------------------- 1 UPDATE 1 UPDATE 9 ``` In plain words: the first `UPDATE` changes one vendor's terms, the one its `WHERE` names. The second has no `WHERE`, so it changes every row it can reach. Row-level security ([19.8](row-level-security.md)) kept the damage inside Cedar Ridge, but all 9 of Cedar Ridge's vendors now say Net 45, and nothing warned anyone. The same is true of `DELETE` with no `WHERE`. Two habits follow. Before any `UPDATE` or `DELETE`, ask the agent to run the same `WHERE` as a `SELECT count(*)`, as the first query here does, and check the count is the one you expect. And treat an `UPDATE` or `DELETE` with no `WHERE` as wrong until someone shows you why it is right. ### How do you read an error? When the database refuses something, read three things in order: the code, the message and the detail. Field guide [F1](field-guide-business-data-and-retrieval.md#f1-postgres-from-zero) walks through the three refusals this chapter meets, with Block 6 to run, and a figure. ### Where does SQL surprise you? Only the differences that cause real bugs, as in Chapter 13: | What you might expect | What SQL does | | --- | --- | | Numbers stored as text sort as numbers | They sort as text: "1290000" comes before "61875" | | A join keeps one row per item on the left | It keeps one row per match, so rows multiply or vanish | | `count(column)` counts rows | It counts values, and skips `NULL` | | `= NULL` finds empty cells | It finds nothing. Use `IS NULL` | | An `UPDATE` asks "which rows?" | With no `WHERE`, it changes every row it can reach | | One `=` sets a value, as in TypeScript | Only after `SET`. Inside `WHERE`, a single `=` compares, as `===` does. SQL has no `==` | ### What do you ask the agent about any query? Your reading partner answers these in a sentence each, and you check each answer against a run: - "Which rows does this touch, and how many?" For a change, "show me the `SELECT count(*)` first." - "Which company is it limited to, and where does that company come from?" ([19.9](one-company-per-transaction.md)) - "What would make it return nothing?" and "What would make it return too much?" - "Explain line 3." Nothing the agent says is accepted on its word. It is accepted when a run, a test or a count agrees. **At work.** In Lab 19, Task 4b, after the companies are loaded, the builder runs [19.2](tables-rows-and-keys-from-zero.md)'s three queries, these five blocks and field guide [F1](field-guide-business-data-and-retrieval.md#f1-postgres-from-zero)'s Block 6 with Claude Code, predicting each answer first. Then comes the Modify step: Claude Code changes Block 4's `IS NULL` to `IS NOT NULL`, and Block 1's `LIMIT 3` to `LIMIT 5`, and the builder predicts both answers before they run. **The operating rule:** read every query before it runs, and predict its answer. Check counts before any change, treat a missing `WHERE` and a `= NULL` as wrong, read an error by its code first, and accept the agent's explanation only when a run agrees. ![A worksheet drawn as four columns: the query, your guess, the real output, and a mark. Row one: an UPDATE with a WHERE naming one vendor, predicted 1 row, real UPDATE 1, a green tick. Row two: the same UPDATE with no WHERE, predicted 1 row, real UPDATE 9, a red cross, and a note saying every Cedar Ridge vendor changed. Row three: WHERE invoice_number = NULL, predicted 1 row, real 0 rows, a red cross, and a note saying nothing equals NULL, use IS NULL. Row four: a join of 9 vendors to their invoices, predicted 9 rows, real 20, a red cross, and a note saying one row per match. Beside the worksheet, the five steps of the method in a column: predict, run, investigate, modify, make. Below: write the guess down first, and where it differs from the output, the query does something you did not expect.](img/chapter-19-predict-then-run.png) *Figure 19.9. Predict, then run. Where your guess and the output differ, the query does something you did not expect.*