SQL NULL Values

What is NULL?

NULL means unknown or missing — it isn't zero or an empty string. In our data, one customer has no email and one has no city; one product has no category.

IS NULL and IS NOT NULL

You can't compare with = NULL — any comparison with NULL is unknown, so no rows match. Use IS NULL:

SELECT name FROM customers WHERE email = NULL;
SELECT name FROM customers WHERE email IS NULL;
SELECT COUNT(*) AS with_city FROM customers WHERE city IS NOT NULL;

name
Margaret Hamilton

with_city
9

COALESCE

COALESCE(a, b, ...) returns the first non-NULL argument — great for display defaults:

SELECT name, COALESCE(email, '(no email)') AS email, COALESCE(city, 'Unknown') AS city
FROM customers WHERE email IS NULL OR city IS NULL;
SELECT COUNT(*) AS all_rows, COUNT(email) AS with_email FROM customers;

name | email | city
Margaret Hamilton | (no email) | Boston
Barbara Liskov | [email protected] | Unknown

all_rows | with_email
10 | 9

NULL in calculations

Any arithmetic with NULL gives NULL, and aggregate functions like COUNT(column) and AVG ignore NULLs.