6. NULL: the "unknown" value

NULL is SQL's way of saying "there's no value here" — unknown, missing, not-applicable. It trips up everyone at first because it doesn't behave like a normal value.

In the rental table, return_date is NULL while a film is still out — it hasn't been returned yet. That's our example of real missing data.

NULL is not equal to anything — not even NULL

Since NULL means "unknown," comparing it with = gives neither true nor false; it gives NULL (treated as "not true"). So this returns zero rows, even though plenty of rentals are still out:

SELECT rental_id
FROM rental
WHERE return_date = NULL;   -- wrong: matches nothing

Use IS NULL and IS NOT NULL instead:

SELECT rental_id, rental_date
FROM rental
WHERE return_date IS NULL;

COALESCE: supply a fallback

COALESCE returns the first value that isn't NULL — perfect for showing a default instead of a blank. Here a person's nickname falls back to their full name when the nickname is missing:

SELECT COALESCE(nickname, name) AS display
FROM (VALUES ('Ada', 'Ada Lovelace'),
             (NULL,  'Grace Hopper')) AS people(nickname, name);

Grace has no nickname, so COALESCE falls through to her name. You can pass more than two arguments — it walks left to right and returns the first non-NULL one.

NULLIF: turn a value into NULL

NULLIF(a, b) returns NULL when a equals b, otherwise a. The classic use is dodging divide-by-zero: x / NULLIF(y, 0) yields NULL instead of an error when y is 0.

Try it

Find the rentals that are still out — the ones with no return_date. Return rental_id, sorted by rental_id.

SELECT rental_id
FROM rental
-- keep only rows where return_date is missing, sorted by rental_id

SELECT rental_id
FROM rental
WHERE return_date IS NULL
ORDER BY rental_id;