Reviewing SQL written by AI assistants

AI coding assistants write SQL that almost always runs and usually looks right, which is exactly why it needs a review: their mistakes return wrong rows, not errors. Reviewing SQL written by AI assistants takes four checks. Read the query against your schema and your engine’s dialect. Run it on a small fixture whose answer you know, with the awkward rows in it: NULLs, ties, empty groups. Read its plan if it will run on large tables. And run it safely: on a read-only connection while you explore, and any change inside a transaction whose row count you check before committing. An assistant has seen a lot of SQL, but it has not seen your data, your constraints or your engine version, and those decide whether a plausible query is a correct one.

Why generated SQL needs a review

In the 2025 Stack Overflow Developer Survey, 84 % of respondents said they use AI tools in their development work or plan to, and more of them distrust the accuracy of those tools (46 %) than trust it (33 %). The most common frustration, named by 66 %, was answers that are almost right but not quite. SQL is a language where almost right is easy to miss: a query that drops one customer or counts one order twice still returns a neat table with plausible numbers. Nothing fails, and the error reaches a report or an invoice.

Treat a generated query like a pull request from a capable colleague who has never seen your database. The colleague knows the language well; you know what the data looks like, which columns can be NULL, which joins multiply rows and which engine runs it.

Six mistakes to look for

• NOT IN with NULLs: x NOT IN (subquery) returns no rows at all once the subquery yields a NULL.
• Join fan-out: joining two one-to-many tables at once multiplies rows, so sums and counts grow.
• The wrong dialect: TOP belongs to SQL Server, LIMIT to PostgreSQL, MySQL and SQLite, and || means OR in MySQL by default.
• Integer division: dividing two integers truncates, so a share of 1 in 3 becomes 0.
• Time-zone boundaries: grouping UTC timestamps by date counts late-evening orders in India on the previous day.
• Missing tie-breakers: ORDER BY ... LIMIT without a unique final key keeps or drops tied rows arbitrarily.

The first two hide in queries about absence and totals, which is where assistants are asked most often. The last four depend on facts about your engine and your data that the question rarely states, so say them in the question: the engine and version, which columns can be NULL, the time zone of the report and what a tie should mean.

One bug, caught by a fixture

The question was “which customers have never placed an order?”, and the shop allows guest checkouts, which are orders without a customer. The assistant’s answer used NOT IN:

-- The question: which customers have never placed an order? Guest checkouts are orders with no customer.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 3), (104, NULL);

-- An assistant's answer: plausible, runs without an error. How many customers does it find?
SELECT count(*) AS customers_found FROM (
  SELECT name FROM customers
  WHERE customer_id NOT IN (SELECT customer_id FROM orders)
);

-- Why it returned nothing: the NULL of the guest order makes NOT IN unknown for every customer.
SELECT 2 NOT IN (1, 3) AS without_null, 2 NOT IN (1, 3, NULL) AS with_null;

-- The fix: NOT EXISTS asks "is there a matching row?", which a NULL cannot confuse.
SELECT c.name AS never_ordered FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

┌─────────────────┐
│ customers_found │
├─────────────────┤
│ 0               │
└─────────────────┘
┌──────────────┬───────────┐
│ without_null │ with_null │
├──────────────┼───────────┤
│ 1            │           │
└──────────────┴───────────┘
┌───────────────┐
│ never_ordered │
├───────────────┤
│ Tenzin Dorjee │
│ Vikram Nair   │
└───────────────┘

The fixture has four customers, two of whom never ordered, and one guest order whose customer_id is NULL. The generated query found no one. The second statement shows why: 2 NOT IN (1, 3) is true, but 2 NOT IN (1, 3, NULL) is unknown, because 2 might equal the unknown value, and WHERE keeps only rows whose condition is true. One NULL in the subquery therefore empties the result. NOT EXISTS asks whether a matching row exists, and found Tenzin Dorjee and Vikram Nair. Without the guest order in the fixture, both queries would have returned the same two names, and the bug would have waited for the first guest checkout in production.

Five more mistakes in assistant-style SQL, reproduced and fixed Pro: opens with a Pro, Premium or Ultimate pass

A review routine: fixtures, plans and safe execution Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• Generated SQL fails silently: review it against your schema, your data and your engine, not by how it reads.
• Look first for NOT IN with NULLs, fan-out, the wrong dialect, integer division, time-zone dates and missing tie-breakers.
• Explore read-only, and run every change in a transaction whose row count you check.