NULL Handling — COALESCE, NULLIF, IFNULL

-- NULL means unknown/missing — it is not 0, not empty string
SELECT NULL = NULL;    -- Returns NULL (not TRUE)
SELECT NULL + 5;       -- Returns NULL
SELECT NULL OR TRUE;   -- Returns TRUE (special case)

-- Only IS NULL / IS NOT NULL works:
SELECT * FROM employees WHERE manager_id IS NULL;

Any arithmetic or comparison with NULL returns NULL. This is called three-valued logic (TRUE, FALSE, NULL). NULL means "we don't know" — so NULL + 5 = unknown.

SELECT name,
       COALESCE(salary, 0) AS salary
FROM employees;

COALESCE returns the first non-NULL value in its list. COALESCE(a, b, c) returns a if not NULL, else b if not NULL, else c. It works across all major databases.

-- IFNULL (MySQL only): takes exactly 2 arguments
SELECT IFNULL(salary, 0) FROM employees;

-- COALESCE (all databases): takes multiple arguments
SELECT COALESCE(salary, bonus, 0) FROM employees;
-- Returns salary if not NULL, else bonus, else 0

Prefer COALESCE for portability — it works in MySQL, PostgreSQL, SQL Server, SQLite. IFNULL is MySQL-specific. COALESCE with multiple args is more powerful.

-- NULLIF(a, b) returns NULL if a = b, else returns a
SELECT NULLIF(salary, 0) FROM employees;
-- Returns NULL instead of 0 (useful to avoid divide-by-zero)

-- Prevent divide by zero:
SELECT revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;

NULLIF is most useful for preventing divide-by-zero errors. If units_sold is 0, NULLIF returns NULL, and dividing by NULL returns NULL instead of an error.

SELECT
  COUNT(*) AS total,
  COUNT(commission) AS has_commission,
  COUNT(*) - COUNT(commission) AS no_commission
FROM employees;

COUNT(column) skips NULLs. So COUNT(*) - COUNT(commission) = number of NULL commission rows. A clean way to profile data completeness.

-- This ignores NULLs in the average (may be wrong):
SELECT AVG(salary) FROM employees;

-- This treats NULL as 0 (divides by ALL employees):
SELECT SUM(COALESCE(salary, 0)) / COUNT(*)
FROM employees;

AVG(salary) skips NULLs — it averages over employees who HAVE a salary. If you want NULLs treated as 0, use SUM/COUNT manually. The business requirement determines which is correct.