Readable SQL and NULL traps

01The problem

Harbourline Freight's sales director asks a simple question: "How many of our customers are not looked after by Obinna?" Obinna Abdullahi (employee 3) manages 15 of the 120 customers. A colleague's query says the answer is 97:

SELECT COUNT(*) AS customers
FROM customers
WHERE account_manager_id <> 3;

120 − 15 is 105. Eight customers have gone missing, and the query gave no error and no warning. It just returned a wrong number, and that number could easily end up in a slide deck.

This course is about the SQL that working analysts write: longer queries, harder questions, and real data that doesn't behave. It starts with the habits that stop queries quietly lying to you.

02The concept

NULL means "unknown", not "nothing"

The 8 missing customers have no account manager: account_manager_id is NULL. SQL treats NULL as an unknown value, so any comparison with it is also unknown:

WHERE keeps a row only when the condition is true, so unknown rows silently vanish. The fix is to say what you mean about NULLs:

WHERE account_manager_id <> 3 OR account_manager_id IS NULL

or replace the NULL first with COALESCE(account_manager_id, 0) <> 3.

The NOT IN trap

NOT IN (subquery) is the most dangerous NULL trap. If the subquery returns even one NULL, NOT IN returns no rows at all, because SQL can't be sure the value isn't equal to the unknown one. NOT EXISTS doesn't have this problem, so prefer it.

Integer division

In SQLite, SQL Server and PostgreSQL, dividing one whole number by another gives a whole number: 7 / 2 is 3, and 2000 / 2411 is 0. Multiply by 100.0 (or 1.0) first to get a decimal. MySQL is the exception: it returns a decimal.

Readable SQL

A query is something other people need to check. Write it so they can:

• One clause per line, with the columns indented under SELECT.
• Short but meaningful aliases: s for shipments, r for routes, never a, b, c.
• A CTE for each step, named for what it holds (delivered, monthly_totals).
• A comment where a definition matters: -- on time = transit days <= route target.

03Example

Three versions of "how many employees have no customers?" Only one is right.

SELECT
  (SELECT COUNT(*)
   FROM employees
   WHERE employee_id NOT IN (SELECT account_manager_id FROM customers)) AS not_in_version,
  (SELECT COUNT(*)
   FROM employees AS e
   WHERE NOT EXISTS (
     SELECT 1 FROM customers AS c WHERE c.account_manager_id = e.employee_id
   )) AS not_exists_version,
  (SELECT COUNT(*)
   FROM employees AS e
   LEFT JOIN customers AS c ON c.account_manager_id = e.employee_id
   WHERE c.customer_id IS NULL) AS left_join_version;

NOT IN says 0. The other two say 16, which is right: only the 8 account managers have customers, so the operations, customs, finance and team-lead staff (16 people) have none. The NOT IN version fails because customers.account_manager_id contains NULLs.

Now integer division. The on-time rate for delivered shipments, written two ways:

-- on time = transit days <= the route's target
SELECT
  SUM(julianday(s.delivery_date) - julianday(s.ship_date) <= r.target_transit_days) / COUNT(*) AS integer_division,
  ROUND(100.0 * SUM(julianday(s.delivery_date) - julianday(s.ship_date) <= r.target_transit_days) / COUNT(*), 1) AS on_time_pct
FROM shipments AS s
JOIN routes AS r ON r.route_id = s.route_id
WHERE s.status = 'Delivered';

The first column says 0%. The second says 76.6%. Same data, same logic: the only difference is 100.0.

04Walkthrough

1. Run the first query in the problem section and note the 97.
2. Change the condition to account_manager_id <> 3 OR account_manager_id IS NULL and check you get 105.
3. In the NOT IN example, change the inner query to SELECT account_manager_id FROM customers WHERE account_manager_id IS NOT NULL. NOT IN now gives 16 too. That's the fix if you must use NOT IN, but NOT EXISTS is safer because it doesn't depend on anyone remembering.
4. In the integer-division example, remove 100.0 * from the second column and watch it fall to 0.

05Practice

06Challenge

07More practice

Optional drills on the same traps.

08Check your understanding