The idea
Most bugs in SQL do not stop the query. A query with a wrong join, a wrong filter or a wrong division still returns rows, and the rows look like data. Debugging means catching a number that is wrong while the query is working.
The method is to read the query in the order the database runs it and, at each clause, ask what one row means now. That meaning is the grain. When the grain is not what you assumed, every number above it is wrong, and you will not get an error.
Figure 1
Read the query in the order the database runs it
-
FROM / JOINwhat does one row mean? -
WHEREwhich rows are dropped? -
GROUP BYwhat collapses? -
HAVINGwhich groups are dropped? -
SELECTis the maths right?
- Start with
FROMand eachJOIN. After them, one row means one of something: an order line, an order-by-session pair. This is the **grain**, and everything below depends on it. WHEREfilters rows before any grouping. Ask which rows it removes, and whether a condition on a joined table quietly turns aLEFT JOINinto anINNER JOIN.GROUP BYcollapses rows. The grouping columns define the new grain. If you wanted one row per day and group by month, the chart is wrong without an error.HAVINGfilters the groups, after the totals exist. A rule about a single row belongs inWHERE; a rule about a total or count belongs here.- Last,
SELECTcomputes the numbers. Check division, rounding and the aggregate against the grain you reached.
Why it exists
On the job, you inherit queries more than you write them: a dashboard query from someone who left, a script pasted in a ticket. Reading and fixing is a skill of its own, and a common interview task.
It is also where analysts lose trust. One report that quietly overcounts shipping revenue, or a retention number nobody can reproduce, costs more than a visible error, because people have already used the number. The habits in this chapter are cheap ways to catch that before anyone sees it.
How it works under the hood
Check the grain first
Fan-out and wrong-grain bugs are the same mistake seen from two sides. After a one-to-many join, an order's columns are repeated once per line, so summing the shipping fee gives a multiple of it. Grouping by the wrong column does the reverse: group an order table by month when the chart needs days and your daily line is one flat point. In both cases the fix starts with writing down the grain you want, and the grain you have.
A concrete case: order 1049 paid $4.99 shipping and has three lines in order_items. Join the two
tables and SUM(shipping_fee) returns 14.97 for that order. Nothing is wrong with the join; the
query is adding up copies. Two honest fixes exist: aggregate the lines to one row per order before
the join, or count each order's fee once with a window or a DISTINCT on the order id. The
check that reveals it is simple. Compare the row count of the join with the row count of the table
whose grain you wanted. If the join has more rows, a one-to-many relationship repeated something.
Know where each filter acts
WHERE sees rows and runs before grouping. HAVING sees groups and runs after. Putting a total in
WHERE fails, but putting a row-level rule in HAVING works while hiding the rows from the
aggregate, which changes the totals. A condition on the right table of a LEFT JOIN, written in
WHERE, discards the NULL rows the join kept.
NULL breaks comparisons
A comparison with NULL is neither true nor false. NOT IN against a list containing even one NULL
never returns true, so the whole result is empty, usually with the second look "nobody has ever
skipped the website".
Figure 2
One NULL in the list and NOT IN returns nothing
| customer_id | first_name |
|---|---|
| 18 | Linda |
| 1262 | Patricia |
| 1269 | Joseph |
| session_id | customer_id |
|---|---|
| 1 | NULL |
| 2 | 18 |
| 3 | NULL |
| customer_id |
|---|
| customer_id |
|---|
| 1262 |
| 1269 |
- Which customers never had a web session?
web_sessions.customer_idis NULL for guests, so the subquery's list contains NULL, here as18, NULL. - Customer 18 (Linda) is in the list, so
NOT INis false and she is dropped. Correct so far. - Customer 1262 never had a session, so they should be kept. But
1262 <> 18is true and1262 <> NULLis unknown, and true AND unknown is unknown.WHEREkeeps only true, so Patricia is dropped, and so is everyone else. NOT EXISTSasks whether a session row matches this customer. NULL never matches anyone, so it cannot poison the answer: customers 1262 and 1269 come back.
Guard against it with NOT EXISTS, or filter IS NOT NULL inside the subquery. The same logic
makes COUNT(col) smaller than COUNT(*) and col <> 'x' drop the NULL rows.
Dates are instants
A timestamp is a moment, not a day. A date written as text means midnight at its start. That is why ranges should be half-open: from the first instant of the first day, up to but not including the first instant of the day after.
Figure 3
BETWEEN stops at midnight on the last day
| order_id | order_date |
|---|---|
| 12975 | 2025-12-30 21:12:55 |
| 12976 | 2025-12-31 05:18:13 |
| 12977 | 2025-12-31 05:29:21 |
| order_id |
|---|
| 12975 |
| order_id |
|---|
| 12975 |
| 12976 |
| 12977 |
order_dateis a timestamp. The text '2025-12-31' is read as 2025-12-31 00:00:00, the first instant of the day.BETWEEN '2025-12-01' AND '2025-12-31'includes everything up to that midnight. Orders made on the 31st at 05:18 are after it, so they are lost.- A half-open range,
>= '2025-12-01' AND < '2026-01-01', keeps every instant of December. On the full table, 752 orders become the correct 777.
Time zones are the same idea. A Black Friday report built in UTC and an ad platform reporting in another zone disagree about which orders belong to which day. Convert once, explicitly, and say which zone the report uses.
Integer division
Dividing two integers can truncate. In DuckDB // does it on purpose, and in Postgres, SQL Server
and Redshift plain / between integers does. A return rate of 6.90% prints as 6. Start every ratio
with 100.0 *, and round only at the end.
Reading a stranger's query
When the query is not yours, resist the urge to rewrite it. Read it once for intent: what question is it meant to answer, and for which period and which rows? Then run it and compare one number with a source you trust. Only after you know it is wrong should you change anything, and change one thing at a time. If you replace three lines at once and the total moves, you will not know which edit mattered or whether the others were needed.
A debugging routine
- Write down the number you expect, from a source you trust.
- Run the query with
COUNT(*)after each join or filter and watch where the count jumps. - Follow one order, one customer or one day through the query by hand.
- Fix one thing, then re-check the number, not just that it runs.
Common misconceptions
"If it ran without an error, the logic is right"
Errors catch syntax and type problems. They say nothing about meaning. The bugs that reach reports are exactly the ones that run: they return a plausible table with a wrong number in it.
"BETWEEN is inclusive, so it covers the whole last day"
It includes the end value, which for a timestamp column is a single instant at the start of the day.
Everything later that day is outside. Use a half-open range with < the next day's start.
"NOT IN and NOT EXISTS are interchangeable"
They agree until the inner column contains a NULL. Then NOT IN returns no rows and NOT EXISTS is
fine. Whenever the subquery can return NULLs, use NOT EXISTS.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. BETWEEN includes both ends. The end value is just 2025-12-31 00:00:00, and orders later on the 31st are after it.Try another answer.
Not quite. It runs on timestamps too, and that is the problem: no error, and a short result.Try another answer.
Not quite. December has 31 days, and the 31st holds the 25 missing orders.Try another answer.
Right. The text becomes a timestamp at the start of that day, so orders at 05:18 on the 31st fall outside. A half-open range with < '2026-01-01' covers the whole last day.
Lesson done: it is ticked on the map.