Six SQL mistakes that give you a wrong number and no error

This article was written with the assistance of an AI writing program, and every number in it comes from running the query on SQLite 3.50.4 and DuckDB 1.5.5.

None of these fail. They return a clean result that happens to be wrong, which is worse. Small tables so you can check the counts by hand. customers has 4 rows, one with a NULL region. orders has 4 rows, one with a NULL amount and one with a NULL customer_id. blocked holds customer 2 and a NULL.

CREATE TABLE customers (id INTEGER, region TEXT);
INSERT INTO customers VALUES (1, 'north'), (2, 'south'), (3, NULL), (4, 'north');
CREATE TABLE orders (id INTEGER, customer_id INTEGER, amount INTEGER, status TEXT);
INSERT INTO orders VALUES (10, 1, 100, 'paid'), (11, 1, NULL, 'paid'), (12, 2, 50, 'refunded'), (13, NULL, 30, 'paid');
CREATE TABLE blocked (customer_id INTEGER);
INSERT INTO blocked VALUES (2), (NULL);

1. NOT IN with a NULL in the list returns nothing.

SELECT COUNT(*) FROM customers WHERE id NOT IN (SELECT customer_id FROM blocked)

Returns 0, not 3. One NULL in the subquery makes every comparison unknown. NOT EXISTS returns 3.

2. COUNT(column) skips NULLs, and AVG divides by that smaller count. COUNT(*) is 4, COUNT(amount) is 3, SUM(amount) is 180, AVG(amount) is 60. If a missing amount really means zero, the average you wanted was 45.

3. A WHERE on the right table of a LEFT JOIN turns it into an inner join.

... LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid'

gives 2 rows. Customers without a paid order vanish. Move the condition into the ON clause and you get 5, every customer kept.

4. <> silently drops NULLs. WHERE region <> 'north' returns 1 row, even though two customers are not in the north. The one with no region is neither equal nor unequal.

5. GROUP BY keeps NULL as its own group. Counts per region come out as NULL 1, north 2, south 1. Useful, but if you then join that back on region, the NULL group matches nothing.

6. The one where the engines disagree. SELECT 7 / 2 returns 3 on SQLite and 3.5 on DuckDB. Integer division depends on the database, so a ratio computed on one can differ on another. Cast to a decimal when it matters.

I write practice questions in this "what does the query return" format and only keep the ones where both engines agree. Twenty are free in the browser, each option explained, at

thibaudlepan77-svg.github.io/interview-questions-verified.

The full set of 502 is a PDF on Ko-fi. The six above stand on their own, and if you see a different number on your database, I'd like to hear which one.

Did this save you a lookup? Buy me a coffee on Ko-fi.