lesson 30 Dates, strings & subqueries · beginner

NOT IN and the NULL trap

Avoid unexpected missing results when NOT IN encounters NULL.

the idea

x NOT IN (list) is true only when x differs from every element. If the list contains NULL, “differs from NULL” is unknown, so NOT IN returns no rows at all. NOT EXISTS is not affected; with NOT IN, remove NULLs from the subquery.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count anonymous cancellations. Run this. Some cancellation orders have customer_id NULL in the source data.the pattern

   example.sql Run

   sql›

   SELECT COUNT(*) AS anonymous_cancellations
   FROM orders
   WHERE order_type = 'cancellation' AND customer_id IS NULL;

2. 2

   See NOT IN return nothing. Run this. When the subquery list includes NULL, NOT IN matches no customers.

   step-2.sql Run

   sql›

   SELECT COUNT(*) AS customers
   FROM customers
   WHERE customer_id NOT IN (
     SELECT customer_id
     FROM orders
     WHERE order_type = 'cancellation'
   );

3. 3

   Answer with NOT EXISTS instead. Run this. NOT EXISTS tests for related rows without the NULL trap that breaks NOT IN.

   step-3.sql Run

   sql›

   SELECT COUNT(*) AS customers_without_sales
   FROM customers c
   WHERE NOT EXISTS (
     SELECT 1
     FROM orders o
     WHERE o.customer_id = c.customer_id AND o.order_type = 'sale'
   );

common mistake

customer_id NOT IN (SELECT customer_id FROM orders WHERE order_type = 'cancellation') returns nothing, because some cancellations are anonymous.