The Most Dangerous SQL Raises No Error

In one line

What is truly frightening in SQL is not syntax errors but three traps that return plausible-looking wrong results without any error.

Why this was needed

A query with wrong syntax tells you right away. What is dangerous is a query that runs. If you read out a number in front of the customer and that number was wrong, every number you say after that in the project comes under suspicion.

There is a reason FDEs are especially exposed to this risk. It is someone else's schema. You write queries without knowing which columns can contain NULL, which relationships are 1:N, and which values are logical-delete markers.

How it works

If you know the three representative ways of being wrong without an error, you avoid most of them.

First, NULL and NOT IN. Say you are looking for customers who have never placed an order.

SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

If there is even one NULL in orders.customer_id, this query returns 0 rows. There is no error and no warning. And the person writing the report writes down, as it is, the business-plausible conclusion that "there is not a single customer without orders".

The cause is three-valued logic. id NOT IN (1, 2, NULL) becomes not true but unknown, and unknown does not pass WHERE. The fix is NOT EXISTS. It only looks at whether rows are returned, so it does not trip over NULL. It is safer to use NOT EXISTS as a habit.

Second, COUNT(*) and COUNT(열). These two are different (the placeholder is the column). COUNT(*) counts rows, and COUNT(열) counts only the rows where that column is not NULL.

The place where this difference shows up most often is LEFT JOIN. A customer with no tickets at all appears in the join result as a single row in which all the ticket-side columns are NULL. Here COUNT(t.id) gives 0 and COUNT(*) gives 1. If you use the latter, a customer with no tickets is counted as having 1 ticket.

For the same reason, COUNT(closed_at) counts only the closed tickets. If there are 80 tickets in total and this value is 60, it means 20 are still open. If you know this when you use it, it is a useful idiom, and if you do not, it is a silent wrong answer.

Third, a WHERE attached to a LEFT JOIN. If you use a LEFT JOIN to preserve all of the left table, and then attach a condition on a column of the right table in the WHERE clause, at that moment all the rows with no match are dropped. This is because NULL is never true in any comparison. The result becomes the same as an INNER JOIN. A condition on the right table must go in the ON clause, not WHERE.

What it looks like in the field

Here you add one more trap peculiar to sqlite. If you load a CSV with .import, all values go in as TEXT. Then WHERE id = 1001 returns 0 rows. This is because what is stored is the string '1001'. A join likewise quietly becomes 0 rows.

If you loaded a CSV and the join result is empty, it is almost always this story. First define the types with CREATE TABLE and then load, or use CAST when querying.

Finally, one practical habit. Before showing an aggregate query to the customer, you check the denominator. How many rows there are in total, and how many of them met the condition. If you report only the ratio, you cannot tell whether it is 1 out of 5 or 10,000 out of 50,000, and the meanings of the two situations are completely different.

Checks to make before presenting a number

Even if you avoid the three traps above, when it is a number to be read out in front of the customer, one more step remains. It is how you will show that the number is right.

Count once more by a different method. Produce the total you got with a join also with a subquery, without a join, and see whether the two values are the same. If the two methods give the same answer, the chance of a mistake drops greatly, and if they differ, the difference itself tells you what you missed. The accident where joining a 1:N relationship and then summing inflates one side is caught here.

Write the denominator and the numerator together. It is the same story as what was said earlier, but when you write it in a report, you go one step further. If you write not "a conversion rate of 20%" but "1 out of 5 (20%)", the reader can judge for themselves how far to trust that number.

Write the period and the reference time. "Last month's sales" is read differently by each person. You have to write which column you cut by (the order time or the payment time), what the time zone is, and even whether the boundaries are included, so that the next person can produce the same number again.

Write what you excluded. If you left out things like test accounts, canceled orders, and requests from internal staff, write that fact together with the counts. Leaving them out is usually right in itself, but if you do not write it down, when the number differs from one produced by someone else, finding the cause takes hours.

Finally, keep the query itself. If you deliver only the result, that number becomes a value that cannot be reproduced a few weeks later. If you keep the query and the execution time together, then when someone later asks "how did this figure come out?", you can answer in seconds, and above all you yourself can tell what you assumed when you look at it again.

What you will do in the next lab

You load a snapshot of a customer support system into sqlite and, starting from ordinary questions such as the number of customers, the confirmed revenue, and the top-revenue customer, produce answers up to questions that need NULL aggregation and LEFT JOIN. The answer to the last question will probably differ from what you expect.