lesson 07 Filtering & sorting · beginner

Understand NULL

Recognize missing values and filter them with IS NULL.

the idea

NULL means a value is unknown or missing. Any comparison with NULL, including = NULL, is unknown, so WHERE drops the row. Test with IS NULL or IS NOT NULL. COALESCE returns the first non-NULL argument and supplies a display value.

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

   Find rows with missing text. Run this. WHERE description IS NULL finds products with no description recorded.

   step-1.sql Run

   sql›

   SELECT stock_code, description, list_price
   FROM products
   WHERE description IS NULL
   LIMIT 10;

2. 2

   Show a label when value is missing. Run this. COALESCE fills in '(no description)' for display without changing stored data.the pattern

   example.sql Run

   sql›

   SELECT stock_code, COALESCE(description, '(no description)') AS label
   FROM products
   WHERE list_price IS NULL;

common mistake

WHERE customer_id = NULL returns nothing. Anonymous orders are found with customer_id IS NULL.