Lesson 6: Handling NULL

In Lesson 0, we briefly introduced NULL -- it means "no value." In this lesson, we learn how NULL behaves specially in SQL and how to handle it safely.

Concept: NULL is "no value." = NULL always returns UNKNOWN, so you must use IS NULL.

NULL Is Not Equal to Anything

You cannot compare NULL with = or <>. Such comparisons always return NULL (unknown) and never become TRUE.

-- Wrong: returns no rows!
SELECT name FROM customers WHERE birth_date = NULL;

-- Correct: use IS NULL
SELECT name FROM customers WHERE birth_date IS NULL;

-- Retrieve customers with confirmed gender
SELECT name, gender
FROM customers
WHERE gender IS NOT NULL
LIMIT 5;

Result:

IS NULL and IS NOT NULL

-- Orders without shipping notes
SELECT order_number, total_amount
FROM orders
WHERE notes IS NULL
LIMIT 5;

Result:

-- Return/complaint orders without an assigned staff member
SELECT order_number, status
FROM orders
WHERE staff_id IS NULL
  AND status IN ('return_requested', 'returned', 'complaints')
LIMIT 5;

COALESCE

COALESCE(a, b, c, ...) returns the first non-NULL argument. It is the most widely used method for displaying a default value instead of NULL.

-- Display 'Not provided' when gender is NULL
SELECT
    name,
    COALESCE(gender, 'Not provided') AS gender_display
FROM customers
LIMIT 8;

Result:

-- Display a default message when there are no shipping notes
SELECT
    order_number,
    COALESCE(notes, 'No special instructions') AS delivery_note
FROM orders
LIMIT 5;

Result:

NULLIF

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It is often used to prevent division by zero errors.

-- Prevent division by zero: safe ratio calculation
SELECT
    grade,
    COUNT(*) AS total,
    COUNT(CASE WHEN is_active = 0 THEN 1 END) AS inactive,
    ROUND(
        100.0 * COUNT(CASE WHEN is_active = 0 THEN 1 END)
              / NULLIF(COUNT(*), 0),
        1
    ) AS pct_inactive
FROM customers
GROUP BY grade;

Result:

Aggregate Functions and NULL

Aggregate functions (SUM, AVG, COUNT(column), MIN, MAX) silently ignore NULL values. Be careful, as this can produce unexpected results.

-- Compare COUNT(*) and COUNT(birth_date)
SELECT
    COUNT(*)           AS all_customers,
    COUNT(birth_date)  AS customers_with_dob,
    AVG(
        CAST(SUBSTR(birth_date, 1, 4) AS INTEGER)
    )                  AS avg_birth_year
FROM customers;

SELECT
    COUNT(*)           AS all_customers,
    COUNT(birth_date)  AS customers_with_dob,
    AVG(YEAR(birth_date)) AS avg_birth_year
FROM customers;

SELECT
    COUNT(*)           AS all_customers,
    COUNT(birth_date)  AS customers_with_dob,
    AVG(EXTRACT(YEAR FROM birth_date))::numeric(6,1) AS avg_birth_year
FROM customers;

Result:

AVG is calculated only for the 4,445 customers who have a birth date. The 785 with NULL are automatically excluded.

NULL Propagation in Expressions

Arithmetic operations involving NULL produce NULL as the result.

-- NULL propagates through operations
SELECT
    1 + NULL,       -- NULL
    NULL * 100,     -- NULL
    'hello' || NULL -- NULL (string concatenation too)

Use COALESCE to prevent NULL propagation.

-- If birth_date is NULL, treat age as -1
SELECT
    name,
    birth_date,
    COALESCE(
        CAST((julianday('now') - julianday(birth_date)) / 365.25 AS INTEGER),
        -1
    ) AS age_years
FROM customers
LIMIT 5;

SELECT
    name,
    birth_date,
    COALESCE(
        TIMESTAMPDIFF(YEAR, birth_date, CURDATE()),
        -1
    ) AS age_years
FROM customers
LIMIT 5;

SELECT
    name,
    birth_date,
    COALESCE(
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date))::int,
        -1
    ) AS age_years
FROM customers
LIMIT 5;

Summary

Practice Problems

Problem 1

From the staff table, retrieve the name, department, and role of employees whose manager_id is NULL (top-level managers).

SELECT name, department, role
FROM staff
WHERE manager_id IS NULL;

Problem 2

From the customers table, retrieve the name and email of customers whose phone is NULL. If email is also NULL, replace it with 'No contact info'.

SELECT
    name,
    COALESCE(email, 'No contact info') AS email
FROM customers
WHERE phone IS NULL;

Problem 3

Using NULLIF, safely calculate the price-to-stock ratio for products in the products table where stock_qty might be 0. Return name, price, stock_qty, and price / NULLIF(stock_qty, 0) with the alias price_per_unit. Limit the result to 5 rows.

SELECT
    name,
    price,
    stock_qty,
    price / NULLIF(stock_qty, 0) AS price_per_unit
FROM products
LIMIT 5;

Problem 4

From the customers table, retrieve the name, email, and created_at of customers whose last_login_at is NULL. Replace NULL email with 'None' and NULL created_at with 'Unknown'. Limit the result to 10 rows.

SELECT
    name,
    COALESCE(email, 'None')        AS email,
    COALESCE(created_at, 'Unknown') AS created_at
FROM customers
WHERE last_login_at IS NULL
LIMIT 10;

Problem 5

Retrieve all orders without an assigned staff member (staff_id IS NULL). Display order_number, status, and notes, replacing NULL notes with '—'.

SELECT
    order_number,
    status,
    COALESCE(notes, '—') AS notes
FROM orders
WHERE staff_id IS NULL
ORDER BY ordered_at DESC
LIMIT 20;

Problem 6

Query the number of customers with confirmed gender and those with unspecified gender, by membership grade. Use COALESCE(gender, 'Unknown') as the grouping basis.

SELECT
    grade,
    COALESCE(gender, 'Unknown') AS gender_status,
    COUNT(*) AS customer_count
FROM customers
GROUP BY grade, COALESCE(gender, 'Unknown')
ORDER BY grade, gender_status;

Problem 7

From the orders table, count the number of orders where cancelled_at is NULL (not cancelled) and where it is NOT NULL (cancelled). Use the aliases not_cancelled and cancelled.

SELECT
    COUNT(CASE WHEN cancelled_at IS NULL THEN 1 END)     AS not_cancelled,
    COUNT(CASE WHEN cancelled_at IS NOT NULL THEN 1 END) AS cancelled
FROM orders;

Problem 8

From the products table, find the number of products with NULL weight_grams and the total product count, then calculate the NULL percentage (1 decimal place). Use the aliases total_products, missing_weight, and pct_missing.

SELECT
    COUNT(*)                                AS total_products,
    COUNT(*) - COUNT(weight_grams)                AS missing_weight,
    ROUND(100.0 * (COUNT(*) - COUNT(weight_grams)) / COUNT(*), 1) AS pct_missing
FROM products;

Problem 9

From the reviews table, calculate the average rating for reviews where content is NULL and where it is NOT NULL. Display COUNT(*) and AVG(rating) together.

SELECT
    CASE WHEN content IS NULL THEN 'No Content' ELSE 'Has Content' END AS content_status,
    COUNT(*)        AS review_count,
    AVG(rating)     AS avg_rating
FROM reviews
GROUP BY CASE WHEN content IS NULL THEN 'No Content' ELSE 'Has Content' END;

Problem 10

Find the count of customers missing birth date, gender, and login history respectively, along with the total customer count.

SELECT
    COUNT(*)                                         AS total_customers,
    COUNT(*) - COUNT(birth_date)                    AS missing_birth_date,
    COUNT(*) - COUNT(gender)                        AS missing_gender,
    SUM(CASE WHEN last_login_at IS NULL THEN 1 ELSE 0 END) AS never_logged_in
FROM customers;

Scoring Guide

Problem Areas:

Next: Lesson 7: CASE Expressions