NULL in SQL

NULL means "no value" or "unknown". A customer with no phone number on file has NULL in the phone column. NULL is not zero and not an empty string, and it behaves differently from every other value, which is why it causes so many subtle bugs.

Syntax

WHERE column IS NULL
WHERE column IS NOT NULL
COALESCE(value1, value2, ...)   -- first non-NULL value
NULLIF(a, b)                    -- NULL if a = b, otherwise a

Why = NULL doesn't work

Any comparison with NULL gives unknown, not true. So this returns no rows, even though some customers have no phone:

SELECT customer_name
FROM customers
WHERE phone = NULL;

Result:

(0 rows)

Examples

1. Find missing values with IS NULL

SELECT customer_name, phone
FROM customers
WHERE phone IS NULL;

Result:

(2 rows)

2. Find present values with IS NOT NULL

Orders that have been delivered (have a delivery date):

SELECT order_id, order_date, delivery_date
FROM orders
WHERE delivery_date IS NOT NULL
LIMIT 5;

Result:

(5 rows)

3. Replace NULL with COALESCE

COALESCE returns the first value that isn't NULL. Show a friendly label instead of an empty cell:

SELECT customer_name,
       COALESCE(phone, 'Not provided') AS phone
FROM customers
LIMIT 4;

Result:

(4 rows)

With more arguments, it works as a fallback chain: COALESCE(phone, email, 'No contact').

SELECT customer_name,
       COALESCE(phone, email, 'No contact') AS best_contact
FROM customers
WHERE phone IS NULL OR email IS NULL;

Result:

(3 rows)

4. NULL in arithmetic

Anything combined with NULL becomes NULL. Here, days to deliver is NULL for orders without a delivery date:

SELECT order_id, status,
       delivery_date - order_date AS days_to_deliver
FROM orders
WHERE order_id BETWEEN 1018 AND 1022;

Result:

(5 rows)

5. NULL in aggregates

COUNT(*) counts rows; COUNT(column) counts non-NULL values:

SELECT COUNT(*) AS customers,
       COUNT(phone) AS with_phone,
       COUNT(email) AS with_email
FROM customers;

Result:

(1 row)

6. Avoid divide-by-zero with NULLIF

NULLIF(a, b) returns NULL when a = b. Dividing by NULL gives NULL instead of an error, which makes it the standard guard against division by zero:

SELECT 100 / NULLIF(0, 0) AS safe_division;

Result:

(1 row)

Without NULLIF, 100 / 0 stops the query with a "division by zero" error.

Common mistakes

• = NULL or <> NULL instead of IS NULL / IS NOT NULL.
• Forgetting NULLs in <> filters. WHERE brand <> 'Nalli' silently drops products with a NULL brand. Add OR brand IS NULL if you need them.
• Averaging with missing values. AVG skips NULLs. If NULL should mean zero, use AVG(COALESCE(col, 0)).

Practice question

List every product's name and brand, showing "Unbranded" when the brand is missing.

SELECT product_name,
       COALESCE(brand, 'Unbranded') AS brand
FROM products
WHERE product_id >= 112;

Key takeaways

• NULL means unknown; it is not 0 or ''.
• Test for it with IS NULL / IS NOT NULL, never =.
• COALESCE replaces NULL with a fallback value.
• NULLIF turns a value into NULL, which is handy for avoiding division by zero.