SQL NULL Handling – IS NULL, COALESCE, NULLIF

What is NULL?

NULL represents an unknown, missing, or inapplicable value. It is not a value itself — it is the absence of a value. SQL uses three-valued logic: expressions evaluate to TRUE, FALSE, or UNKNOWN (which is what you get when NULL is involved in a comparison).

SQL

-- Any comparison with NULL returns UNKNOWN, not TRUE or FALSE
SELECT NULL = NULL;      -- UNKNOWN (not TRUE!)
SELECT NULL = 0;         -- UNKNOWN
SELECT NULL != NULL;     -- UNKNOWN
SELECT NULL > 5;         -- UNKNOWN

-- Arithmetic with NULL always returns NULL
SELECT 5 + NULL;         -- NULL
SELECT NULL * 100;       -- NULL
SELECT 'Hello' || NULL;  -- NULL (PostgreSQL)
SELECT CONCAT('Hello', NULL);  -- NULL (MySQL) or 'Hello' depending on version

⚠️

WHERE col = NULL never matches anything

This is the most common NULL mistake. WHERE manager_id = NULL will return zero rows because the comparison evaluates to UNKNOWN, which is not TRUE. Always use IS NULL or IS NOT NULL.

IS NULL and IS NOT NULL

The only correct way to test for NULL is with IS NULL and IS NOT NULL. These are the only predicates that handle the UNKNOWN state correctly.

SQL

-- Find employees without a manager (top-level managers)
SELECT employee_id, name
FROM employees
WHERE manager_id IS NULL;

-- Find employees who have a manager assigned
SELECT employee_id, name
FROM employees
WHERE manager_id IS NOT NULL;

-- Find customers with missing email addresses
SELECT customer_id, name
FROM customers
WHERE email IS NULL;

-- Combine NULL and non-NULL checks
SELECT *
FROM orders
WHERE ship_date IS NULL      -- not yet shipped
  AND order_date < CURDATE() - INTERVAL 7 DAY;  -- ordered more than 7 days ago

COALESCE()

COALESCE(v1, v2, v3, ...) returns the first non-NULL value from the list. It is ANSI SQL and works in all databases. Use it to substitute default values for NULLs.

SQL

SELECT COALESCE(NULL, NULL, 'first non-null', 'ignored');  -- 'first non-null'
SELECT COALESCE(42, 'fallback');                           -- 42

-- Replace NULL bonus with 0
SELECT
  employee_id,
  name,
  salary,
  COALESCE(bonus, 0)           AS bonus,
  salary + COALESCE(bonus, 0)  AS total_comp
FROM employees;

-- Cascade: try phone, then mobile, then email as contact
SELECT
  customer_id,
  COALESCE(phone, mobile, email, 'No contact info') AS contact
FROM customers;

-- Use COALESCE to fix NULL-propagating CONCAT
SELECT
  CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name
FROM employees;

NULLIF()

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. Its primary use is preventing division-by-zero errors.

SQL

SELECT NULLIF(5, 5);    -- NULL  (values are equal)
SELECT NULLIF(5, 3);    -- 5     (values differ, return first arg)
SELECT NULLIF(0, 0);    -- NULL

-- Prevent division by zero
SELECT
  product_id,
  total_revenue,
  units_sold,
  total_revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;
-- When units_sold = 0, NULLIF returns NULL, and NULL / anything = NULL
-- This avoids a "division by zero" error crashing the query

-- Treat empty string as NULL
SELECT NULLIF(TRIM(notes), '') AS notes
FROM orders;  -- empty or whitespace-only notes become NULL

IFNULL() and NVL()

These are database-specific shortcuts for the common COALESCE(val, fallback) pattern with exactly two arguments.

SQL

-- MySQL: IFNULL(value, fallback_if_null)
SELECT IFNULL(NULL, 'default');     -- 'default'
SELECT IFNULL('hello', 'default');  -- 'hello'

SELECT IFNULL(bonus, 0) AS bonus
FROM employees;  -- same as COALESCE(bonus, 0)

-- Oracle / some others: NVL(value, fallback)
SELECT NVL(commission, 0) FROM employees;

-- PostgreSQL: no IFNULL — use COALESCE
SELECT COALESCE(bonus, 0) AS bonus FROM employees;

-- SQL Server: ISNULL(value, fallback)
SELECT ISNULL(manager_id, 0) AS manager FROM employees;

NULL in GROUP BY and Ordering

NULLs in GROUP BY form their own group — all NULL values are treated as equal for grouping purposes. In ORDER BY, NULLs sort last by default in most databases (except PostgreSQL where NULLs are last by default for DESC, first for ASC).

SQL

-- NULLs form their own group in GROUP BY
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;
-- Employees with NULL department_id are grouped together as a single NULL group

-- Control NULL sort order in PostgreSQL
SELECT name, manager_id
FROM employees
ORDER BY manager_id NULLS LAST;    -- NULLs at bottom
ORDER BY manager_id NULLS FIRST;   -- NULLs at top

-- MySQL: NULLs sort first in ASC by default
-- Force NULLs last in MySQL
SELECT name, manager_id
FROM employees
ORDER BY manager_id IS NULL, manager_id;   -- FALSE(0) sorts before TRUE(1)

Summary

📋 Summary

NULL = unknown/missing — Not zero, not empty string. Any comparison with NULL yields UNKNOWN.
Three-valued logic — SQL conditions can be TRUE, FALSE, or UNKNOWN. WHERE keeps only TRUE rows.
IS NULL / IS NOT NULL — The only correct way to check for NULL.
COALESCE(v1, v2, ...) — Returns first non-NULL. ANSI standard, use everywhere.
NULLIF(a, b) — Returns NULL when a = b. Key use: prevent division-by-zero.
IFNULL() / NVL() / ISNULL() — Database-specific two-argument COALESCE alternatives.
NULL in GROUP BY — NULL values group together as their own single group.
Aggregates ignore NULLs — Except COUNT(*) which counts all rows.

FAQ

Why does NULL = NULL return UNKNOWN and not TRUE?+

NULL means "unknown value". If you compare two unknown values, you cannot determine whether they are equal — they might be the same or different. SQL follows this logic strictly: comparing NULL with anything (including itself) results in UNKNOWN, not TRUE or FALSE. That is why IS NULL exists as a special predicate.

What is the difference between COALESCE and IFNULL?+

COALESCE accepts two or more arguments and is ANSI SQL — it works in all databases. IFNULL is MySQL-specific and only takes exactly two arguments. Functionally, IFNULL(a, b) is identical to COALESCE(a, b). Prefer COALESCE for portability and when you need more than two fallback options.

How does NOT IN behave with NULLs?+

This is a subtle trap. If the IN list contains a NULL, NOT IN returns no rows — because x != NULL is UNKNOWN, and UNKNOWN is not TRUE. For example: WHERE id NOT IN (1, 2, NULL) returns zero rows regardless of the data. Always filter NULLs from subqueries used with NOT IN, or use NOT EXISTS instead which handles NULLs correctly.

Can a PRIMARY KEY column contain NULL?+

No — PRIMARY KEY columns are implicitly NOT NULL. The purpose of a primary key is to uniquely identify each row, which requires a defined (non-unknown) value. If you declare a column as PRIMARY KEY, the database will reject NULL inserts. For optional identifiers, use a UNIQUE constraint with nullable columns, but be aware that UNIQUE allows multiple NULLs in most databases (since NULL != NULL).