SQL Null Functions

In SQL, NULL represents a missing or unknown value — it is not zero, not an empty string, and not “false.” Because ordinary operators and comparisons behave strangely around NULL, SQL provides a small set of dedicated null functions — COALESCE, NULLIF, and dialect-specific helpers like IFNULL — that let you detect, replace, or work around missing data cleanly. Mastering these functions is essential: forgetting how NULL propagates through expressions is one of the most common sources of subtly wrong query results.

Overview: What NULL Means and How Functions Handle It

A database column can hold NULL when a value is unknown, not yet entered, or not applicable — for example, a customer who hasn’t provided a phone number, or an employee bonus that hasn’t been set. SQL uses three-valued logic: every comparison evaluates to TRUE, FALSE, or UNKNOWN. Any arithmetic or comparison involving NULL produces NULL (which the WHERE clause treats as “not a match”). This is why salary = NULL never matches anything, even for rows where salary genuinely is NULL — you must ask “is this unknown?” with IS NULL, not “does this equal unknown?” with =.

Null functions exist to make this manageable. COALESCE and IFNULL let you substitute a fallback value when a column is NULL, so reports and calculations don’t break or display blank cells. NULLIF does the reverse — it deliberately turns a value into NULL when a condition is met, which is the standard trick for avoiding division-by-zero errors. Aggregate functions like COUNT, SUM, and AVG also have special NULL-handling rules built in: they silently skip NULL values rather than letting them poison the whole calculation, except for COUNT(*), which counts rows regardless of any NULLs inside them.

Syntax

COALESCE(expr1, expr2, ..., exprN)
IFNULL(expr1, expr2)          -- SQLite / MySQL
NULLIF(expr1, expr2)
expr IS NULL
expr IS NOT NULL

Function / Operator	What it does	Dialect notes
COALESCE(a, b, c, ...)	Returns the first argument that is not NULL, scanning left to right. If every argument is NULL, returns NULL.	Standard ANSI SQL — works the same in SQLite, MySQL, PostgreSQL, SQL Server, Oracle.
IFNULL(a, b)	Two-argument shortcut: returns a if it isn’t NULL, otherwise returns b.	SQLite and MySQL only. SQL Server uses ISNULL(a, b); Oracle uses NVL(a, b). Not ANSI standard — prefer COALESCE for portable code.
NULLIF(a, b)	Returns NULL if a equals b; otherwise returns a.	Standard ANSI SQL, supported everywhere.
expr IS NULL	Tests whether an expression is unknown/missing. Evaluates to true/false, unlike = NULL.	Standard everywhere; the only correct way to test for NULL.
expr IS NOT NULL	The negation of IS NULL.	Standard everywhere.

Examples

Example 1: Falling back to an alternate column with COALESCE

CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT);
INSERT INTO contacts (id, name, phone, email) VALUES
  (1, 'Alice', '555-0101', 'alice@example.com'),
  (2, 'Bob', NULL, 'bob@example.com'),
  (3, 'Carla', '555-0303', NULL),
  (4, 'Dan', NULL, NULL);

SELECT name, COALESCE(phone, email, 'No contact info') AS best_contact
FROM contacts;

Result:

name	best_contact
Alice	555-0101
Bob	bob@example.com
Carla	555-0303
Dan	No contact info

COALESCE checks phone first; if it’s NULL it falls through to email; if that’s also NULL it falls through to the literal string. Dan has neither, so he gets the final fallback. This pattern — “use this, or that, or a default” — is the most common use of COALESCE.

Example 2: Replacing NULL before doing arithmetic

CREATE TABLE orders_demo (order_id INTEGER PRIMARY KEY, customer TEXT, amount REAL, discount REAL);
INSERT INTO orders_demo (order_id, customer, amount, discount) VALUES
  (1, 'Acme', 100.00, 10.00),
  (2, 'Globex', 200.00, NULL),
  (3, 'Initech', 150.00, 5.00);

SELECT order_id, customer, amount, IFNULL(discount, 0) AS discount,
       amount - IFNULL(discount, 0) AS net_total
FROM orders_demo;

Result:

order_id	customer	amount	discount	net_total
1	Acme	100.0	10.0	90.0
2	Globex	200.0	0	200.0
3	Initech	150.0	5.0	145.0

Without IFNULL, amount - discount for order 2 would evaluate to NULL (since anything minus NULL is NULL), silently wiping out the row’s total. Wrapping discount in IFNULL(discount, 0) treats a missing discount as zero, which is what the business actually means.

Example 3: Using NULLIF to avoid division by zero

CREATE TABLE inventory (product TEXT, total_sold INTEGER, total_orders INTEGER);
INSERT INTO inventory (product, total_sold, total_orders) VALUES
  ('Widget', 50, 10),
  ('Gadget', 0, 5),
  ('Gizmo', 30, 0);

SELECT product, total_sold, total_orders,
       total_sold * 1.0 / NULLIF(total_orders, 0) AS avg_per_order
FROM inventory;

Result:

product	total_sold	total_orders	avg_per_order
Widget	50	10	5.0
Gadget	0	5	0.0
Gizmo	30	0	NULL

For Gizmo, total_orders is 0, so NULLIF(total_orders, 0) converts it to NULL before the division happens. Dividing by NULL yields NULL instead of throwing a divide-by-zero error, which is exactly the safe behavior you want in a report. (Note: SQLite actually returns NULL for straight division by zero too, but many other databases — and application code reading the result — raise a hard error, so NULLIF is the portable, intentional way to guard the calculation.)

Example 4: How aggregate functions treat NULL

CREATE TABLE feedback (id INTEGER PRIMARY KEY, rating INTEGER);
INSERT INTO feedback (id, rating) VALUES
  (1, 5), (2, NULL), (3, 3), (4, NULL), (5, 4);

SELECT COUNT(*) AS total_rows, COUNT(rating) AS rated_rows,
       AVG(rating) AS avg_rating, SUM(rating) AS sum_rating
FROM feedback;

Result:

total_rows	rated_rows	avg_rating	sum_rating
5	3	4.0	12

COUNT(*) counts all five rows regardless of content, but COUNT(rating) only counts the three rows where rating is not NULL. SUM and AVG likewise ignore NULL rows entirely — they don’t treat them as zero. This is a frequent source of confusion: a NULL rating pulls the row out of the average rather than dragging the average down.

How It Works Step by Step (Under the Hood)

When the query engine evaluates a row, it processes clauses in this logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Null functions typically appear in the SELECT list or inside a WHERE/HAVING condition, and how they’re evaluated depends on where they sit:

• In the WHERE clause: the engine evaluates the boolean expression for every candidate row. If any part of that expression involves a bare comparison to NULL (like discount = NULL), the result is UNKNOWN, and rows where the condition is UNKNOWN are excluded — just like rows where it’s FALSE. This is why IS NULL exists as a separate operator: it short-circuits the three-valued logic and returns a real TRUE/FALSE.
• In the SELECT list: COALESCE, IFNULL, and NULLIF are evaluated per row, after the row has already passed the WHERE filter. COALESCE evaluates its arguments left to right and stops at the first non-NULL value (short-circuit evaluation), so you can safely put an expensive expression last if it’s rarely needed.
• In aggregates: functions such as SUM, AVG, MIN, MAX, and COUNT(column) filter out NULL values from their input set before computing the result. Only COUNT(*) is an exception, since it counts rows, not values.

Internally, most engines represent NULL with a separate “null bitmap” or marker alongside the stored value, rather than a special in-band value, which is why NULL can appear in a column of any type (integer, text, date) without conflicting with real data.

Common Mistakes

Mistake 1: Comparing to NULL with = instead of IS NULL

CREATE TABLE contacts2 (id INTEGER, name TEXT, phone TEXT);
INSERT INTO contacts2 VALUES (1, 'Eve', NULL), (2, 'Frank', '555-9999');
SELECT * FROM contacts2 WHERE phone = NULL;

Result: 0 rows returned. This query runs without an error, which makes the mistake easy to miss — but phone = NULL always evaluates to UNKNOWN, never TRUE, so no row can ever match it, even Eve’s row where phone really is NULL.

CREATE TABLE contacts2 (id INTEGER, name TEXT, phone TEXT);
INSERT INTO contacts2 VALUES (1, 'Eve', NULL), (2, 'Frank', '555-9999');
SELECT * FROM contacts2 WHERE phone IS NULL;

Result: 1 row: (1, 'Eve', NULL). Using IS NULL correctly identifies the row with a missing phone number.

Mistake 2: Using a non-portable NULL function like ISNULL()

CREATE TABLE staff (id INTEGER, name TEXT, department TEXT);
INSERT INTO staff VALUES (1, 'Grace', 'Engineering'), (2, 'Hank', NULL);
SELECT name, ISNULL(department, 'Unassigned') FROM staff;

This fails with an error such as no such function: ISNULL. ISNULL() is a SQL Server function, not part of the ANSI standard, and SQLite (like MySQL and PostgreSQL) doesn’t implement it. Oracle has the same problem with its own NVL() function — none of these three names are portable across databases.

CREATE TABLE staff (id INTEGER, name TEXT, department TEXT);
INSERT INTO staff VALUES (1, 'Grace', 'Engineering'), (2, 'Hank', NULL);
SELECT name, COALESCE(department, 'Unassigned') AS department FROM staff;

Result: Grace | Engineering, Hank | Unassigned. COALESCE does the same job and is understood by every major SQL database, so it’s the safer default choice.

Best Practices

• Always use IS NULL / IS NOT NULL to test for NULL — never = NULL or <> NULL, which silently match nothing.
• Prefer COALESCE over dialect-specific functions (IFNULL, ISNULL, NVL) when you want your SQL to be portable across databases.
• Remember that COALESCE accepts any number of arguments and returns the first non-NULL one — useful for chaining several fallback sources.
• Use NULLIF(x, 0) as a standard guard whenever you divide by a column that might legitimately be zero.
• Don’t assume SUM or AVG treat NULL as zero — they exclude NULL rows from the calculation entirely, which can change an average significantly.
• When writing a NOT IN subquery, watch out for NULL: if the subquery’s result set contains even one NULL, the whole NOT IN comparison can unexpectedly return no rows. Filter with WHERE column IS NOT NULL inside the subquery, or use NOT EXISTS instead.
• Document why a column allows NULL (unknown vs. not applicable vs. not yet collected) so future readers of the schema understand what it means.

Practice Exercises

• Exercise 1: Using the contacts table from Example 1, write a query that returns each person’s name and a column called reachable that shows their phone number, or their email if the phone is missing, or the text 'Unreachable' if both are missing.
• Exercise 2: Using the feedback table from Example 4, write a query that returns the total number of feedback rows, the number of ratings actually submitted, and the number of missing ratings (hint: subtract COUNT(rating) from COUNT(*)).
• Exercise 3: Using the inventory table from Example 3, write a query that calculates total_sold / total_orders for every product, using NULLIF so that a product with zero orders shows NULL instead of causing an error. What result do you expect for Gizmo?

Summary

• NULL means “unknown” and follows three-valued logic — comparisons involving NULL return UNKNOWN, not TRUE or FALSE.
• Use IS NULL / IS NOT NULL to test for NULL; = NULL never matches anything.
• COALESCE(a, b, c, ...) returns the first non-NULL argument and is the ANSI-standard, portable choice.
• IFNULL (SQLite/MySQL), ISNULL (SQL Server), and NVL (Oracle) are two-argument, dialect-specific equivalents of a simple COALESCE.
• NULLIF(a, b) turns a value into NULL when it equals b — the standard guard against division by zero.
• Aggregate functions like SUM, AVG, MIN, MAX, and COUNT(column) skip NULL values; only COUNT(*) counts every row unconditionally.