Lesson 1.3

NULL: missing values

Find, filter and fill in missing values without losing rows.

Imagine you're a data analyst at Amazon. The customers and orders tables have some missing values.

1Find missing values with IS NULL

NULL means there's no value: it isn't 0 and it isn't empty text. Find it with IS NULL, and the opposite with IS NOT NULL.

SELECT customer_id, first_name, address
FROM customers
WHERE address IS NULL;

Practice

Find customers whose city is missing.

Return: customer id, first name and country

Write your query here

Comparing anything with NULL gives "unknown", not true. So WHERE address = NULL quietly returns zero rows.

SELECT customer_id, address
FROM customers
WHERE address = NULL;

Practice

This query should list orders that didn't use a promo code, but it returns nothing. Fix it.

Return: order id and promo code

SELECT order_id, promo_code
FROM orders
WHERE promo_code = NULL;

promo_code <> 'SPRING10' skips orders with no code, because NULL isn't "not equal" to anything either. Add OR promo_code IS NULL to keep them.

SELECT order_id, promo_code
FROM orders
WHERE promo_code <> 'SPRING10'
   OR promo_code IS NULL;

Practice

Find every order that didn't use the code PRIME20, including orders with no code at all.

Return: order id and promo code

Write your query here

COALESCE(a, b) returns a, or b when a is NULL. Use it to show a default instead of a blank.

SELECT customer_id, COALESCE(city, 'unknown') AS city
FROM customers;

Practice

Show each order's promo code, with 'none' when there isn't one.

Return: order id and promo code

Write your query here