lesson 07 Filtering & sorting · beginner

Understand NULL

Recognize missing values and filter them with IS NULL.

the idea

NULL means missing or unknown. NULL = NULL is unknown, not true. Use IS NULL and IS NOT NULL. COALESCE returns the first non-NULL argument.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT name, COALESCE(city, 'Unknown') AS city
FROM customers;

Watch for: NULL is not the string 'NULL', and it is not an empty string.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Find missing cities. Some city values are blank in the sense of NULL. Equality to NULL never matches; IS NULL does.

   step-1.sql Run

   sql›

   SELECT name, city
   FROM customers
   WHERE city IS NULL;

2. 2

   Replace NULL for display. COALESCE returns the first non-NULL argument. Missing cities become the label Unknown without changing stored rows.

   step-2.sql Run

   sql›

   SELECT name, COALESCE(city, 'Unknown') AS city
   FROM customers;