Working with NULL — Sets and functions

What is NULL?

NULL represents the absence of a value: "unknown" or "not applicable". It is not zero nor an empty string. Any arithmetic operation with NULL yields NULL (price + 10 is NULL if price is NULL).

Three-valued logic

In SQL a condition can be true, false or unknown. Because NULL is "unknown", comparing with it never yields true:

SELECT * FROM products WHERE price = NULL;   -- ❌ returns nothing

Even if price is NULL, NULL = NULL is unknown, not true. That is why there are specific operators:

SELECT * FROM products WHERE price IS NULL;       -- unknown prices
SELECT * FROM products WHERE price IS NOT NULL;   -- known prices

Functions to handle NULL

COALESCE(a, b, c, ...) returns the first argument that is not NULL. Ideal for providing a default value:
```
SELECT name, COALESCE(price, 0) AS final_price FROM products;
```
IFNULL(a, b) is the two-argument version of COALESCE: if a is NULL, it returns b.
NULLIF(a, b) does the opposite: it returns NULL if a = b, and a otherwise. Useful for "turning into NULL" a sentinel value (for example, treating a 0 as "no data") or to avoid division by zero:
```
SELECT total / NULLIF(units, 0) AS average_price FROM sales;
```

Examples

Replace NULL with 0 using COALESCE

SELECT name, COALESCE(price, 0) AS final_price
FROM products
ORDER BY id;