NULL in SQL: IS NULL, COALESCE and Common Traps

The short answer

NULL is not zero and not an empty string. It means "value unknown or missing". Because the value is unknown, SQL cannot say whether it equals anything — even another NULL. That is why:

SELECT * FROM users WHERE phone = NULL;   -- always returns nothing
SELECT * FROM users WHERE phone IS NULL;  -- correct

Use IS NULL and IS NOT NULL for checks, COALESCE to substitute a value, and NOT NULL constraints to prevent missing data where it makes no sense.

Three-valued logic

SQL conditions have three outcomes: TRUE, FALSE and UNKNOWN. Any comparison with NULL (=, <>, >, <) gives UNKNOWN. A WHERE clause keeps only rows where the condition is TRUE, so UNKNOWN rows silently disappear.

A typical surprise: WHERE status <> 'blocked' does not return rows where status is NULL. If you want them, say so: WHERE status <> 'blocked' OR status IS NULL. PostgreSQL and some others also offer IS DISTINCT FROM, which treats NULL as a comparable value.

NULL in aggregates

• COUNT(*) counts rows. COUNT(column) counts only non-NULL values. Different numbers in the same report are often this.
• SUM and AVG ignore NULLs. AVG(discount) averages only rows where a discount is filled, not all orders.
• If all values are NULL (or there are no rows), SUM returns NULL, not 0.

SELECT
  COUNT(*)                    AS orders,
  COUNT(discount)             AS orders_with_discount,
  AVG(discount)               AS avg_among_discounted,
  AVG(COALESCE(discount, 0))  AS avg_across_all_orders,
  COALESCE(SUM(discount), 0)  AS total_discount
FROM orders;

Which AVG is right depends on the business question. Decide explicitly instead of letting NULL decide.

NULL in JOINs

• NULL keys never match in a join, not even NULL to NULL. Rows with a missing manager_id will not join to anything.
• A LEFT JOIN fills missing right-side columns with NULL. If you then filter on a right-side column in WHERE (WHERE p.status = 'paid'), those NULL rows are dropped and the LEFT JOIN quietly becomes an inner join. Put such conditions in ON when you want to keep all left rows.

The NOT IN trap

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If even one customer_id in orders is NULL, this query returns no rows at all: id <> NULL is UNKNOWN for every customer. Use NOT EXISTS, which behaves as expected:

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

NULL and UNIQUE constraints

In most databases (PostgreSQL, MySQL, SQLite) a UNIQUE column can hold many NULLs, because NULLs are not considered equal. SQL Server allows only one NULL in a unique constraint. If you need "only one row without a value", use a NOT NULL column with a default, or the database's specific option (PostgreSQL has NULLS NOT DISTINCT in recent versions).

Tools that fix wrong reports

• COALESCE(a, b, c) returns the first non-NULL value: COALESCE(nickname, first_name, 'Guest').
• NULLIF(a, b) returns NULL when a equals b. The classic use is avoiding division by zero: revenue / NULLIF(visits, 0).
• NOT NULL with DEFAULT in the schema: is_active BOOLEAN NOT NULL DEFAULT true, qty INTEGER NOT NULL DEFAULT 0. The best NULL handling is not storing NULL where it has no meaning.
• Be aware of string behaviour: in standard SQL 'a' || NULL is NULL, MySQL's CONCAT returns NULL if any argument is NULL, and Oracle treats an empty string as NULL.
• Sorting differs too: some databases put NULLs first, others last. Use NULLS FIRST / NULLS LAST where supported.

Checklist for a new table

• Mark a column NULL-able only if "unknown" is a real state.
• Give flags, counters and amounts a NOT NULL default.
• In reports, decide for each metric whether NULL means 0, "exclude" or "unknown".
• Replace NOT IN (subquery) with NOT EXISTS.