SQL 49 🛢️ Handling NULLs with COALESCE and NULLIF - KandZ

NULL is not a value. It is the absence of a value. It does not equal anything—not even itself. It does not compare greater or less than anything. It propagates through arithmetic and string operations, turning everything it touches into NULL. This behavior is correct according to the SQL standard, but it is inconvenient in practice. Queries need to substitute a default value when a column is NULL, or convert a specific value into NULL when it should be treated as absent. SQL provides two functions for this: COALESCE and NULLIF.

This chapter covers both functions in full. You will learn how COALESCE returns the first non-NULL value from a list, how NULLIF returns NULL when two values are equal, and how the two functions complement each other. You will see the practical patterns where they appear: default values, safe division, conditional aggregation, and data cleaning. You will also see how they differ from CASE and when one is clearer than the other.

Key point: COALESCE(a, b, c, ...) returns the first non-NULL argument. NULLIF(a, b) returns NULL if a equals b, and a otherwise. Both are expressions that produce a value and can be used anywhere a value is valid. They are standard SQL and are supported by PostgreSQL, SQL Server, Oracle, MySQL, and SQLite.

Why COALESCE and NULLIF exist

The null propagation problem. In SQL, any operation involving NULL produces NULL. NULL + 5 is NULL. 'Hello ' || NULL is NULL. NULL > 10 is NULL, which is treated as false in a WHERE clause. This propagation is mathematically consistent but often not what the application wants. A report that sums a column with NULLs produces NULL for the total instead of a number. A display that concatenates a first and last name produces NULL when either is missing. COALESCE provides the substitution that turns NULL into a usable value.

The default value problem. A column may be nullable because the value is unknown at insert time, but the application may want a default when reading. A phone number that is NULL might display as “Not provided.” A quantity that is NULL might be treated as zero. COALESCE expresses this: COALESCE(phone, 'Not provided') or COALESCE(quantity, 0).

The sentinel value problem. Some schemas use a sentinel value to represent “no value”—an empty string, a zero, a date far in the future. When the sentinel should be treated as NULL, NULLIF converts it. NULLIF(status, 'Unknown') turns the string 'Unknown' into NULL, which can then be handled by COALESCE or by aggregate functions that ignore NULL.

The safe division problem. Division by zero produces an error in most databases. Division by NULL produces NULL. To avoid the error, NULLIF is used to convert zero to NULL before division: a / NULLIF(b, 0). If b is zero, the expression is NULL instead of an error. If b is non-zero, the division proceeds normally.

The conditional aggregation problem. Aggregate functions ignore NULL. COUNT(column) counts non-NULL values. SUM(column) sums non-NULL values. NULLIF can be used to exclude specific values from an aggregate: SUM(NULLIF(amount, 0)) sums only non-zero amounts. COALESCE can be used to provide a default when the aggregate returns NULL: COALESCE(SUM(amount), 0).

The readability problem. Both functions can be expressed with CASE. COALESCE(a, b) is CASE WHEN a IS NOT NULL THEN a ELSE b END. NULLIF(a, b) is CASE WHEN a = b THEN NULL ELSE a END. The functions are shorter and more declarative. They express the intent directly, without the boilerplate of CASE.

a. COALESCE: the first non-NULL value

COALESCE takes two or more arguments and returns the first one that is not NULL. If all arguments are NULL, it returns NULL.

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- Result: 'third'

SELECT COALESCE(NULL, NULL, NULL);
-- Result: NULL

SELECT COALESCE('first', 'second');
-- Result: 'first'

The function is variadic—it accepts any number of arguments. The arguments are evaluated in order, and the first non-NULL is returned. The remaining arguments are not evaluated.

The most common use is providing a default value for a nullable column:

SELECT
    customer_id,
    COALESCE(phone, 'Not provided') AS phone,
    COALESCE(email, 'No email on file') AS email
FROM customers;

When phone is NULL, the result is 'Not provided'. When it is not NULL, the result is the phone number.

COALESCE can also combine columns, returning the first that is populated:

SELECT
    customer_id,
    COALESCE(mobile_phone, home_phone, work_phone, 'No phone') AS primary_phone
FROM customers;

This returns the mobile phone if it exists, otherwise the home phone, otherwise the work phone, otherwise the string 'No phone'. This is the standard pattern for “preferred contact” or “fallback” logic.

In aggregates, COALESCE handles the case where the aggregate returns NULL. SUM of an empty set is NULL, not zero:

SELECT
    customer_id,
    COALESCE(SUM(amount), 0) AS total_spent
FROM orders
GROUP BY customer_id;

Without COALESCE, a customer with no orders would have total_spent as NULL. With it, the value is 0.

COALESCE is also used with LEFT JOIN to substitute a default for rows that did not match:

SELECT
    c.customer_id,
    c.name,
    COALESCE(o.order_count, 0) AS order_count
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) o ON o.customer_id = c.customer_id;

Customers with no orders get a count of 0 instead of NULL.

b. NULLIF: converting a value to NULL

NULLIF takes two arguments. If the first equals the second, it returns NULL. Otherwise, it returns the first.

SELECT NULLIF(5, 5);       -- Result: NULL
SELECT NULLIF(5, 3);       -- Result: 5
SELECT NULLIF('a', 'a');   -- Result: NULL
SELECT NULLIF('a', 'b');   -- Result: 'a'

The most common use is safe division. Division by zero is an error in most databases. To avoid it, convert zero to NULL before dividing:

SELECT
    total_amount,
    quantity,
    total_amount / NULLIF(quantity, 0) AS unit_price
FROM line_items;

When quantity is zero, NULLIF(quantity, 0) is NULL, and the division produces NULL instead of an error. When quantity is non-zero, the division proceeds normally.

NULLIF is also used to convert sentinel values to NULL. If a schema uses an empty string to represent “no value,” NULLIF(column, '') converts it to NULL, which aggregates and comparisons handle correctly:

SELECT
    COUNT(*) AS total,
    COUNT(NULLIF(middle_name, '')) AS with_middle_name
FROM persons;

The COUNT(*) counts all rows. The COUNT(NULLIF(middle_name, '')) counts rows where middle_name is not an empty string, because NULLIF converts empty strings to NULL, and COUNT ignores NULL.

NULLIF is also used to prevent division errors in percentage calculations:

SELECT
    successful,
    total,
    100.0 * successful / NULLIF(total, 0) AS success_rate
FROM stats;

If total is zero, the rate is NULL instead of an error.

NULLIF is the inverse of COALESCE. Where COALESCE replaces NULL with a value, NULLIF replaces a value with NULL. They are often used together:

SELECT
    COALESCE(NULLIF(TRIM(name), ''), 'Anonymous') AS display_name
FROM users;

The NULLIF(TRIM(name), '') trims whitespace and converts an empty string to NULL. The COALESCE(..., 'Anonymous') converts NULL to a default. The result is a display name that is never empty and never NULL.

c. COALESCE and NULLIF in practice

The two functions are complementary. COALESCE fills in missing values. NULLIF removes unwanted values. Together they handle the common cases of data cleaning and display formatting.

Default values in reports:

SELECT
    product_id,
    name,
    COALESCE(stock_quantity, 0) AS stock,
    COALESCE(price, 0.00) AS price
FROM products;

Safe arithmetic:

SELECT
    a,
    b,
    a + COALESCE(b, 0) AS sum,
    a / NULLIF(b, 0) AS quotient
FROM numbers;

Conditional counting:

SELECT
    department,
    COUNT(*) AS total,
    COUNT(NULLIF(status, 'Inactive')) AS active
FROM employees
GROUP BY department;

Fallback columns:

SELECT
    COALESCE(display_name, username, email, 'Guest') AS name
FROM users;

Removing sentinel values:

SELECT
    AVG(NULLIF(score, -1)) AS average_score
FROM responses;

If -1 is used as a sentinel for “no response,” NULLIF(score, -1) converts it to NULL, and AVG ignores it. Without NULLIF, the sentinel value would be included in the average.

Combining both:

SELECT
    customer_id,
    COALESCE(NULLIF(TRIM(phone), ''), 'Not provided') AS phone
FROM customers;

The NULLIF converts empty strings to NULL. The COALESCE converts NULL to a default. The result is a phone number that is never empty and never NULL.

In WHERE clauses:

SELECT * FROM orders
WHERE COALESCE(discount, 0) > 0;

This returns orders where the discount is greater than zero, treating NULL as zero. Without COALESCE, orders with a NULL discount would be excluded, because NULL > 0 is NULL, not true.

In ORDER BY:

SELECT * FROM products
ORDER BY COALESCE(display_order, 9999);

Products with a NULL display_order sort last instead of first. In most databases, NULLs sort first in ascending order by default. COALESCE substitutes a large number so they sort last.

Complete Example Session

-- ============================================
-- PART 1: BASIC COALESCE
-- ============================================
SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- Result: 'third'

SELECT COALESCE(NULL, NULL);
-- Result: NULL

SELECT COALESCE('first', 'second');
-- Result: 'first'

-- ============================================
-- PART 2: COALESCE FOR DEFAULT VALUE
-- ============================================
SELECT
    customer_id,
    COALESCE(phone, 'Not provided') AS phone
FROM customers;
-- NULL phone becomes 'Not provided'

-- ============================================
-- PART 3: COALESCE WITH MULTIPLE FALLBACKS
-- ============================================
SELECT
    customer_id,
    COALESCE(mobile, home, work, 'No phone') AS primary_phone
FROM customers;
-- Returns first non-NULL, or 'No phone'

-- ============================================
-- PART 4: COALESCE WITH AGGREGATES
-- ============================================
SELECT
    customer_id,
    COALESCE(SUM(amount), 0) AS total_spent
FROM orders
GROUP BY customer_id;
-- Customers with no orders get 0 instead of NULL

-- ============================================
-- PART 5: BASIC NULLIF
-- ============================================
SELECT NULLIF(5, 5);       -- NULL
SELECT NULLIF(5, 3);       -- 5
SELECT NULLIF('a', 'a');   -- NULL
SELECT NULLIF('a', 'b');   -- 'a'

-- ============================================
-- PART 6: NULLIF FOR SAFE DIVISION
-- ============================================
SELECT
    total_amount,
    quantity,
    total_amount / NULLIF(quantity, 0) AS unit_price
FROM line_items;
-- Division by zero returns NULL instead of error

-- ============================================
-- PART 7: NULLIF FOR SENTINEL VALUES
-- ============================================
SELECT
    COUNT(*) AS total,
    COUNT(NULLIF(middle_name, '')) AS with_middle_name
FROM persons;
-- Empty strings converted to NULL are not counted

-- ============================================
-- PART 8: NULLIF FOR PERCENTAGES
-- ============================================
SELECT
    successful,
    total,
    100.0 * successful / NULLIF(total, 0) AS success_rate
FROM stats;
-- Zero total returns NULL instead of error

-- ============================================
-- PART 9: COMBINING COALESCE AND NULLIF
-- ============================================
SELECT
    COALESCE(NULLIF(TRIM(name), ''), 'Anonymous') AS display_name
FROM users;
-- Trims whitespace, converts empty to NULL, then to 'Anonymous'

-- ============================================
-- PART 10: COALESCE IN WHERE AND ORDER BY
-- ============================================
SELECT * FROM orders
WHERE COALESCE(discount, 0) > 0;
-- NULL discount treated as 0

SELECT * FROM products
ORDER BY COALESCE(display_order, 9999);
-- NULL display_order sorts last

The ten parts covered basic COALESCE, defaults, multiple fallbacks, aggregates, basic NULLIF, safe division, sentinel values, percentages, combining both functions, and COALESCE in WHERE and ORDER BY.

Quick Reference

COALESCE

Aspect	Value
Arguments	Two or more
Returns	First non-NULL argument
All NULL	Returns NULL
Evaluation	Left to right, stops at first non-NULL
Use case	Default values, fallback columns
Standard	Yes

NULLIF

Aspect	Value
Arguments	Exactly two
Returns	NULL if equal, first argument otherwise
Use case	Safe division, sentinel removal
Standard	Yes

Function Comparison

Function	Purpose	Example
`COALESCE`	Replace NULL with value	`COALESCE(phone, 'N/A')`
`NULLIF`	Replace value with NULL	`NULLIF(status, 'Unknown')`

CASE Equivalents

Function	CASE Equivalent
`COALESCE(a, b)`	`CASE WHEN a IS NOT NULL THEN a ELSE b END`
`NULLIF(a, b)`	`CASE WHEN a = b THEN NULL ELSE a END`

Common Patterns

Pattern	Purpose
`COALESCE(col, 0)`	Default zero
`COALESCE(col, '')`	Default empty string
`COALESCE(a, b, c)`	First available value
`COALESCE(SUM(x), 0)`	Zero for empty aggregate
`a / NULLIF(b, 0)`	Safe division
`NULLIF(col, '')`	Empty string to NULL
`NULLIF(col, -1)`	Sentinel to NULL
`COUNT(NULLIF(col, ''))`	Conditional count

Best Practices

✅ Do This:

-- Use COALESCE for defaults
SELECT COALESCE(phone, 'Not provided') FROM customers;        -- ✅

-- Use COALESCE with aggregates
SELECT COALESCE(SUM(amount), 0) FROM orders;                   -- ✅

-- Use NULLIF for safe division
SELECT total / NULLIF(quantity, 0) FROM line_items;            -- ✅

-- Combine for trimming and defaulting
SELECT COALESCE(NULLIF(TRIM(name), ''), 'Anonymous') FROM users; -- ✅

-- Use COALESCE in ORDER BY for null positioning
ORDER BY COALESCE(priority, 9999)                              -- ✅

-- Use COALESCE in WHERE to treat NULL as zero
WHERE COALESCE(discount, 0) > 0                                -- ✅

❌ Don’t Do This:

-- Don't use COALESCE with a single argument
SELECT COALESCE(phone) FROM customers;                         -- ❌

-- Don't use NULLIF for NULL substitution
SELECT NULLIF(phone, '') FROM customers;                       -- ⚠️ wrong direction

-- Don't forget that NULLIF(0, 0) returns NULL
SELECT 1 / NULLIF(0, 0);                                       -- NULL, not error

-- Don't nest COALESCE unnecessarily
SELECT COALESCE(COALESCE(a, b), c) FROM t;                     -- ⚠️ use COALESCE(a, b, c)

-- Don't use COALESCE to mask data quality issues
SELECT COALESCE(required_field, 'MISSING') FROM t;             -- ⚠️ fix the data

Common Pitfalls

Pitfall	Why It Happens	Fix
`SUM` returns NULL	Empty group	`COALESCE(SUM(x), 0)`
Division by zero error	Zero denominator	`a / NULLIF(b, 0)`
Empty string counted	Not NULL	`NULLIF(col, '')`
`COALESCE` wrong order	First non-NULL wins	Order most-preferred first
`NULLIF` returns unexpected NULL	Values equal	Check the comparison
NULL sorts first	Default sort behavior	`COALESCE(col, 9999)`

Real-World Examples

1. Display Default

COALESCE(phone, 'Not provided')

2. Zero for Empty Sum

COALESCE(SUM(amount), 0)

3. Safe Division

total / NULLIF(quantity, 0)

4. Fallback Columns

COALESCE(mobile, home, work, 'No phone')

5. Percentage

100.0 * successful / NULLIF(total, 0)

6. Conditional Count

COUNT(NULLIF(status, 'Inactive'))

7. Trim and Default

COALESCE(NULLIF(TRIM(name), ''), 'Anonymous')

8. Treat NULL as Zero

WHERE COALESCE(discount, 0) > 0

9. Sort Nulls Last

ORDER BY COALESCE(priority, 9999)

10. Sentinel Removal

AVG(NULLIF(score, -1))

Visual

COALESCE Evaluation

┌─────────────────────────────────────────────────────────────┐
│  COALESCE(a, b, c, d)                                       │
│                                                             │
│  Evaluate a:                                                │
│    ├── NOT NULL ──▶ return a, stop                          │
│    └── NULL ──▶ continue                                    │
│                                                             │
│  Evaluate b:                                                │
│    ├── NOT NULL ──▶ return b, stop                          │
│    └── NULL ──▶ continue                                    │
│                                                             │
│  Evaluate c:                                                │
│    ├── NOT NULL ──▶ return c, stop                          │
│    └── NULL ──▶ continue                                    │
│                                                             │
│  Evaluate d:                                                │
│    ├── NOT NULL ──▶ return d, stop                          │
│    └── NULL ──▶ return NULL                                 │
│                                                             │
│  Example: COALESCE(NULL, 'b', 'c') → 'b'                    │
│                                                             │
└─────────────────────────────────────────────────────────────┘

NULLIF Evaluation

┌─────────────────────────────────────────────────────────────┐
│  NULLIF(a, b)                                               │
│                                                             │
│  Is a equal to b?                                           │
│    ├── YES ──▶ return NULL                                  │
│    └── NO  ──▶ return a                                     │
│                                                             │
│  Examples:                                                  │
│  NULLIF(5, 5)      → NULL                                   │
│  NULLIF(5, 3)      → 5                                      │
│  NULLIF('a', 'a')  → NULL                                   │
│  NULLIF('a', 'b')  → 'a'                                    │
│  NULLIF(0, 0)      → NULL                                   │
│  NULLIF(NULL, 5)   → NULL                                   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Safe Division

┌─────────────────────────────────────────────────────────────┐
│  WITHOUT NULLIF                                             │
│                                                             │
│  SELECT 10 / 0;                                             │
│  → ERROR: division by zero                                  │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  WITH NULLIF                                                │
│                                                             │
│  SELECT 10 / NULLIF(0, 0);                                  │
│  → NULLIF(0, 0) = NULL                                      │
│  → 10 / NULL = NULL                                         │
│  → Result: NULL (no error)                                  │
│                                                             │
│  SELECT 10 / NULLIF(5, 0);                                  │
│  → NULLIF(5, 0) = 5                                         │
│  → 10 / 5 = 2                                               │
│  → Result: 2                                                │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Combining COALESCE and NULLIF

┌─────────────────────────────────────────────────────────────┐
│  COALESCE(NULLIF(TRIM(name), ''), 'Anonymous')              │
│                                                             │
│  Input: '  Alice  '                                         │
│    TRIM → 'Alice'                                           │
│    NULLIF('Alice', '') → 'Alice'                            │
│    COALESCE('Alice', 'Anonymous') → 'Alice'                 │
│                                                             │
│  Input: '   '                                               │
│    TRIM → ''                                                │
│    NULLIF('', '') → NULL                                    │
│    COALESCE(NULL, 'Anonymous') → 'Anonymous'                │
│                                                             │
│  Input: NULL                                                │
│    TRIM(NULL) → NULL                                        │
│    NULLIF(NULL, '') → NULL                                  │
│    COALESCE(NULL, 'Anonymous') → 'Anonymous'                │
│                                                             │
│  Result: never empty, never NULL.                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
`COALESCE`	Returns first non-NULL argument
`COALESCE` arguments	Two or more
`COALESCE` all NULL	Returns NULL
`NULLIF`	Returns NULL if equal, first argument otherwise
`NULLIF` arguments	Exactly two
Safe division	`a / NULLIF(b, 0)`
Default value	`COALESCE(col, default)`
Fallback columns	`COALESCE(a, b, c)`
Empty aggregate	`COALESCE(SUM(x), 0)`
Sentinel removal	`NULLIF(col, sentinel)`
Conditional count	`COUNT(NULLIF(col, value))`

Key takeaways:

COALESCE returns the first non-NULL argument. It is used for default values, fallback columns, and handling the NULL result of aggregates on empty sets. It accepts any number of arguments and evaluates left to right.
NULLIF returns NULL when the two arguments are equal. It is used for safe division, sentinel value removal, and conditional counting. It always takes exactly two arguments.
The two functions are complementary. COALESCE replaces NULL with a value. NULLIF replaces a value with NULL. They are often combined: COALESCE(NULLIF(col, ''), 'default') converts empty strings to NULL and then to a default.
COALESCE(SUM(x), 0) handles empty aggregates. SUM of an empty set is NULL. In a report, this is usually not the desired result. COALESCE converts it to zero.
a / NULLIF(b, 0) prevents division by zero errors. NULLIF converts zero to NULL, and division by NULL produces NULL instead of an error. The expression is safe to evaluate on any data.
NULLIF converts sentinel values to NULL. If a schema uses empty strings, -1, or 'Unknown' to represent missing data, NULLIF converts them to NULL, which aggregates and comparisons handle correctly.
NULL sorts first in most databases. To sort NULLs last, use ORDER BY COALESCE(col, 9999) with a value larger than any real value. To sort them first, use COALESCE(col, -1).
The functions can be expressed with CASE. COALESCE(a, b) is CASE WHEN a IS NOT NULL THEN a ELSE b END. NULLIF(a, b) is CASE WHEN a = b THEN NULL ELSE a END. The functions are shorter and express the intent more directly.

Remember: NULL is not a value. It is the absence of a value, and it propagates through operations in ways that are mathematically consistent but practically inconvenient. COALESCE and NULLIF are the two functions that make NULL manageable. COALESCE fills in what is missing. NULLIF removes what is unwanted. Together they handle default values, safe arithmetic, conditional aggregation, and data cleaning. They are standard SQL, supported everywhere, and can be expressed with CASE when needed. Use them whenever a query would otherwise produce NULL in a place where a value is expected, or a value in a place where NULL is expected.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!