How to Review AI-Generated SQL Before You Trust the Number

An engineer outlines five quick checks for validating AI-generated SQL queries, warning that fluent-looking queries can hide errors like join fan-out and NULL handling. The checks, which take about two minutes and require only the existing database, catch the four most common mistakes AI-written SQL makes, such as double-counting rows and returning NULL instead of a number.

An AI assistant will write you a query in ten seconds, the query will run, and the number that comes back will look completely reasonable. This page gives you the five checks that tell you whether that number is right. They take about two minutes, they need no tools beyond the database you already have, and they catch the four mistakes AI-written SQL actually makes. The order matters. The checks are arranged cheapest first, so the first one costs a single row count and the last one costs a short conversation. Most wrong queries fall to the first two. The short version. A query that runs has only passed a grammar check. The number is right when the rows, the filters and the denominator match the question you asked. The database only takes a query as far as the first gate. Before the list: what do you think the database actually checks when it accepts a query? Grammar. That is the whole list. Spell a table name wrong and you get an error. Sum the wrong column, join in a way that doubles rows, or filter after grouping when the question needed it before, and you get a clean result set with a wrong number in it. Every mistake on this page is valid SQL. AI assistants add one specific difficulty: their queries are fluent. The aliases are tidy, the formatting is clean, and the shape looks like something a careful person wrote. Fluency reads as correctness, and it is not the same thing. Treat an AI query the way you would treat a first draft from a new colleague: with respect, and with the row counts open. Everything below runs on one small shop dataset, so every number can be checked by hand. Thirteen orders in July, five customers, and a refunds table where two orders were refunded in two parts. Eleven of the thirteen orders are completed; one is refunded, one is pending. There is also a staff accounts table listing internal accounts, and it contains one NULL row, because real lookup tables usually do. The gross value of the eleven completed orders is 1,605. Total refunds are 275. Hold on to those two numbers. Before the answer: eleven completed orders, five refund rows. After a LEFT JOIN from orders to refunds, does the query see eleven rows, or more? Here is the query an assistant wrote for "net revenue from completed orders": SELECT SUM o.amount - SUM COALESCE r.refund amount, 0 AS net revenue FROM orders o LEFT JOIN refunds r ON r.order id = o.order id WHERE o.status = 'completed'; It runs. It returns 1,830 . The right answer is 1,330 , which you already know, because 1,605 minus 275 is 1,330. The join is the problem. Two orders were each refunded in two parts, so each of those orders matches two refund rows. The join turns eleven rows into thirteen, and SUM o.amount counts those two orders twice: 2,105 instead of 1,605. The extra 500 is exactly the value of the two double-counted orders. This is called fan-out: a join multiplies rows whenever the key on the other side appears more than once. The check costs two counts: SELECT COUNT FROM orders WHERE status = 'completed'; -- 11 SELECT COUNT FROM orders o LEFT JOIN refunds r ON r.order id = o.order id WHERE o.status = 'completed'; -- 13 That one comparison decides it. If the second number grew, the join fanned out and every SUM or AVG over the left table's columns is suspect. If it held, the join is safe and you move on. The next request was "the same revenue, excluding staff accounts". The assistant wrote: SELECT SUM amount FROM orders WHERE status = 'completed' AND customer id NOT IN SELECT customer id FROM staff accounts ; This returns NULL , from zero rows. Not a smaller number. Nothing. Say out loud why one NULL in staff accounts could empty the whole result, before reading on. Here is the mechanism. NOT IN asks, for each order, "is this customer different from every value in the list?" One of the values in the list is NULL, and SQL cannot say whether anything is different from NULL. The comparison comes back unknown, unknown is not true, and no row survives. One NULL row in a lookup table silently empties the result. The fix is either to keep NULL out of the list, or to use NOT EXISTS , which does not have this behavior: SELECT SUM amount FROM orders o WHERE o.status = 'completed' AND NOT EXISTS SELECT 1 FROM staff accounts s WHERE s.customer id = o.customer id ; -- 1,395 The reviewer's habit: for every column a filter touches, ask what happens to that filter when the column is NULL. The same blindness sinks = NULL , which is covered in NULL in SQL https://michaelnocito.github.io/analyst-prep-kit/guides/sql-null/ . The request was "customers who spent more than 400 on completed orders". Which condition should remove rows before the grouping, and which should test the finished totals? The assistant's version: SELECT c.name, SUM o.amount AS total FROM orders o JOIN customers c ON c.customer id = o.customer id GROUP BY c.name HAVING SUM o.amount 400; It returns three customers: Ellis at 450, Diaz at 420, Boone at 420. The right answer is Ellis alone . Diaz only crosses 400 because a pending order was counted. Boone only crosses it because a refunded order was counted. The query never filtered on status, so the grouping summed everything. The reviewed version filters rows first, then tests the totals: SELECT c.name, SUM o.amount AS total FROM orders o JOIN customers c ON c.customer id = o.customer id WHERE o.status = 'completed' GROUP BY c.name HAVING SUM o.amount 400; -- Ellis, 450 The rule to review against: WHERE decides which rows are allowed into the groups, HAVING decides which finished groups are allowed into the result. An AI query that mentions a status, a date range or a segment only in HAVING , or not at all, deserves a second look. The full mechanics are in GROUP BY and HAVING https://michaelnocito.github.io/analyst-prep-kit/guides/sql-group-by-having/ . The request was "average order value for completed orders". Two queries, both fluent, both running clean: -- version A SELECT AVG amount FROM orders WHERE status = 'completed'; -- version B SELECT AVG cust avg FROM SELECT AVG amount AS cust avg FROM orders WHERE status = 'completed' GROUP BY customer id ; Version A returns 145.91 . Version B returns 152.50 . Neither is broken. They divide by different things. A divides the total by eleven orders. B averages five per-customer averages, which hands a customer with three small orders the same weight as a customer with two large ones. Which one is right depends entirely on the question. "What does a typical order look like" is A. "What does a typical customer's order look like" is B. The assistant picked one without asking, because it had to pick something. The reviewer's question is always the same: divided by what? If you cannot answer it from the query, the query is not done. The same trap in spreadsheet form is in percentages and pivot tables https://michaelnocito.github.io/analyst-prep-kit/guides/excel-pivot-percentages/ . The four checks above are mechanical. The last one catches everything else, and it uses the assistant itself. Ask it to restate, clause by clause, what the query does and why each clause serves the question you asked. Not a summary. One line per clause, in the query's own order. A wrong paraphrase points at the wrong clause with surprising reliability, because the model has to commit to a claim about each piece instead of describing the whole. This is the same read-out-loud block from the teaching-comment format https://michaelnocito.github.io/analyst-prep-kit/guides/sql-teaching-comments/ , used as a review tool. The reviewed query from Check 1 looks like this when it carries its comment: / WHY: Net July revenue from completed orders. Refunds arrive in parts, so refunds are totaled per order BEFORE the join. Joining the raw refunds table doubles multi-refund orders 13 rows vs 11 . / WITH refund totals AS SELECT order id, SUM refund amount AS refunded FROM refunds GROUP BY order id SELECT SUM o.amount - SUM COALESCE rt.refunded, 0 AS net revenue FROM orders o LEFT JOIN refund totals rt ON rt.order id = o.order id WHERE o.status = 'completed'; -- 1,330 Picture the last query an AI wrote for you at work. Walk it through Check 1 in your head: what would the row count be before its join, and after? If you cannot answer from memory, that is the query to run the checks on tomorrow. DISTINCT inside an aggregate is a signal, not a fix. When an AI writes SUM DISTINCT amount , it usually met fan-out and silenced the symptom. Two different orders for 75 collapse into one, and the total is wrong in a new direction. Pre-aggregate in a CTE instead, as in Check 5. Sometimes fan-out is the point. Joining orders to line items should multiply rows, because the question lives at line level. The check is not "did rows grow", it is "did rows grow when the question did not ask them to". The NULL behavior of NOT IN is standard SQL, not a quirk of one engine. SQLite, PostgreSQL, MySQL and SQL Server all do it. Fixing it by cleaning the lookup table works until the next import adds a NULL back. NOT EXISTS stays fixed. The premise of this page, that AI SQL runs but is often wrong, is measured, not anecdotal. On the BIRD benchmark, 12,751 questions over 95 real databases, the strongest model tested in 2023 reached 54.89 percent execution accuracy. Human engineers reached 92.96 percent on the same questions. Execution accuracy means the query's result matched the correct result, so nearly every failure in that gap is a query that ran and returned a wrong answer Li et al., 2023, Advances in Neural Information Processing Systems 36, Datasets and Benchmarks track . Models have improved since, and the gap has narrowed, not closed. The checks on this page are aimed at the failure modes that benchmark surfaced: wrong joins, wrong filters, wrong aggregation grain. If you have paper nearby, sketch the orders and refunds tables from Check 1 and draw one line from each completed order to its refund rows. The two orders that get two lines are the whole story of fan-out, and having drawn it once, you will see it in a query before you run it. | Check | Run or ask | Failing looks like | |---|---|---| | 1. Rows | COUNT before and after each join | Row count grew; sums over the left table inflated | | 2. NULL | What is NULL in each filtered column? | NOT IN returns nothing; = NULL matches nothing | | 3. Filter seat | Is each condition in WHERE or HAVING, and should it be? | Status or date named only after grouping, or missing | | 4. Denominator | Divided by what? | Average of averages; percentage of the wrong whole | | 5. Read-back | One line per clause, against the question | A clause the paraphrase gets wrong or skips | Count the rows before and after every join. It is the cheapest check on the page, it catches the most expensive mistake, and it works on human SQL exactly as well as it works on the machine's. What is the most recent number an AI handed you that you passed along without checking, and which of the five would have caught it if it was wrong? Every number here was run before it was published. The dataset is small on purpose, so you can rebuild it and check each result by hand. The 1,830, the empty result, the three-customer list and both averages are real outputs, not illustrations. Want the wider skill rather than the checklist? SQL for Analysts reads queries line by line in everyday words, which is the habit these checks are built from. SQL for Analysts, $19 → https://michaelnocito.gumroad.com/l/sql-for-analysts?utm source=analyst-prep-kit&utm medium=guide&utm campaign=sql-for-analysts Originally published on Analyst Prep Kit: How to Review AI-Generated SQL Before You Trust the Number Visit the site for more beginner data analysis guides and free resources: the full guide archive covers SQL, Excel, Power BI, Tableau, Python and statistics, and the practice kits run in your browser with nothing to install. If it was useful: Buy Me a Coffee.