SQL COALESCE, NULLIF, IFNULL for Handling NULL

NULL is one of the most misunderstood concepts in SQL. It does not mean zero. It does not mean an empty string. It means unknown or absent. A customer whose phone number is NULL might have a phone; you just do not know the number. An order whose ship date is NULL has not been shipped yet. This distinction matters because NULL behaves differently from every other value in SQL, and ignoring that behavior leads to silent bugs that corrupt calculations, break comparisons, and produce wrong results without any error message.

SQL COALESCE and its companion functions NULLIF, IFNULL, and ISNULL are the tools that let you handle NULL values safely and predictably. They replace missing values with defaults, create NULLs strategically, and keep your queries from falling into the many traps that NULL sets for unsuspecting developers.

This guide explains how NULL actually works, covers every major NULL-handling function with practical examples, and provides the strategies you need to write queries that produce correct results even when data is incomplete.

Why NULL Is Different from Everything Else

Before diving into functions, you need to understand why NULL requires special handling. Consider these facts:

SELECT NULL = NULL;       -- Result: NULL (not TRUE!)
SELECT NULL != NULL;      -- Result: NULL (not TRUE!)
SELECT NULL > 0;          -- Result: NULL
SELECT NULL + 10;         -- Result: NULL
SELECT NULL || 'hello';   -- Result: NULL (PostgreSQL)
SELECT NOT NULL;          -- Result: NULL

NULL is not equal to anything, including itself. Any operation involving NULL produces NULL. Any comparison with NULL returns NULL (which is treated as FALSE in WHERE clauses). This is called three-valued logic: every boolean expression evaluates to TRUE, FALSE, or UNKNOWN (which is what NULL represents in boolean context).

This creates real problems:

-- Bug: this misses rows where phone IS NULL
SELECT * FROM contacts WHERE phone != '555-0100';
-- Rows with phone = NULL are NOT included because NULL != '555-0100' is UNKNOWN

-- Bug: this sum ignores NULLs silently
SELECT SUM(bonus) FROM employees;
-- If some bonuses are NULL, they are skipped, not treated as 0

-- Bug: this count is wrong
SELECT COUNT(email) FROM contacts;
-- Only counts rows where email IS NOT NULL
-- COUNT(*) counts all rows, COUNT(column) skips NULLs

The Sample Data

All examples use these tables:

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    phone         VARCHAR(20),
    city          VARCHAR(50),
    loyalty_points INT
);

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    price         DECIMAL(10,2),
    sale_price    DECIMAL(10,2),
    description   VARCHAR(500),
    stock_qty     INT
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    ship_date     DATE,
    discount_pct  DECIMAL(5,2),
    notes         VARCHAR(200),
    total_amount  DECIMAL(10,2)
);

INSERT INTO customers VALUES
(1, 'Alice',  'Martin', 'alice@example.com', '555-0101', 'New York',  250),
(2, 'Bob',    'Jones',  'bob@example.com',   NULL,       'Chicago',    NULL),
(3, 'Carol',  'Smith',  NULL,                '555-0103', NULL,         100),
(4, 'Dave',   'Wilson', 'dave@example.com',  '555-0104', 'Boston',     0),
(5, 'Eve',    'Turner', NULL,                NULL,       'Denver',     NULL);

INSERT INTO products VALUES
(101, 'Wireless Mouse',      25.99, 19.99, 'Ergonomic wireless mouse',       150),
(102, 'USB-C Cable',          9.99, NULL,  'Braided 2m cable',               300),
(103, 'Notebook A5',          4.50, 3.99,  NULL,                             500),
(104, 'Mechanical Keyboard', 89.99, NULL,  'Cherry MX switches',              0),
(105, 'Desk Lamp',           34.50, 29.99, '',                                 3),
(106, 'Standing Desk',      299.99, NULL,  'Height-adjustable standing desk', 12);

INSERT INTO orders VALUES
(1001, 1, '2024-01-15', '2024-01-18', 10.00, 'Gift wrap requested',  155.97),
(1002, 2, '2024-03-22', '2024-03-25', NULL,  NULL,                   299.99),
(1003, 1, '2024-06-05', NULL,         5.00,  'Fragile items',         45.48),
(1004, 3, '2024-06-10', NULL,         NULL,  NULL,                    89.99),
(1005, 4, '2024-06-14', NULL,         0.00,  '',                       9.99);

note

Notice the deliberately messy data: NULL phones, emails, cities, sale prices, descriptions, ship dates, discounts, and notes throughout.

COALESCE: The Essential NULL Replacement

COALESCE returns the first non-NULL value from a list of arguments. It is the most important NULL-handling function in SQL and is supported by every major database.

Syntax

COALESCE(value1, value2, value3, ...)

The function evaluates arguments left to right and returns the first one that is not NULL. If all arguments are NULL, it returns NULL.

Basic Examples

SELECT
    COALESCE(NULL, 'fallback')          AS example1,
    COALESCE(NULL, NULL, 'third')       AS example2,
    COALESCE('first', 'second')         AS example3,
    COALESCE(NULL, NULL, NULL)          AS example4;

example1	example2	example3	example4
fallback	third	first	NULL

Providing Default Values for NULL Columns

The most common use of COALESCE: replace NULL with a meaningful default.

SELECT
    first_name,
    COALESCE(email, 'No email on file') AS email,
    COALESCE(phone, 'No phone on file') AS phone,
    COALESCE(city, 'Unknown')           AS city,
    COALESCE(loyalty_points, 0)         AS loyalty_points
FROM customers;

first_name	email	phone	city	loyalty_points
Alice	alice@example.com	555-0101	New York	250
Bob	bob@example.com	No phone on file	Chicago	0
Carol	No email on file	555-0103	Unknown	100
Dave	dave@example.com	555-0104	Boston	0
Eve	No email on file	No phone on file	Denver	0

Every NULL is replaced with a human-readable default. The query never returns NULL for any of these columns.

Fallback Chain: Multiple Alternatives

COALESCE accepts any number of arguments, creating a priority chain of fallback values:

-- Use sale_price if available, otherwise regular price
SELECT
    product_name,
    price AS regular_price,
    sale_price,
    COALESCE(sale_price, price) AS effective_price
FROM products;

product_name	regular_price	sale_price	effective_price
Wireless Mouse	25.99	19.99	19.99
USB-C Cable	9.99	NULL	9.99
Notebook A5	4.50	3.99	3.99
Mechanical Keyboard	89.99	NULL	89.99
Desk Lamp	34.50	29.99	29.99
Standing Desk	299.99	NULL	299.99

Three-level fallback for contact information:

-- Best available contact method
SELECT
    first_name,
    COALESCE(email, phone, 'No contact info') AS best_contact
FROM customers;

first_name	best_contact
Alice	alice@example.com
Bob	bob@example.com
Carol	555-0103
Dave	dave@example.com
Eve	No contact info

Alice, Bob, and Dave have emails (checked first). Carol has no email but has a phone (checked second). Eve has neither, so the default string is used (third).

COALESCE in Calculations

NULL in arithmetic makes the entire result NULL. COALESCE prevents this:

-- Without COALESCE: NULL discount makes the total NULL
SELECT
    order_id,
    total_amount,
    discount_pct,
    total_amount * (1 - discount_pct / 100) AS wrong_total
FROM orders;

order_id	total_amount	discount_pct	wrong_total
1001	155.97	10.00	140.373
1002	299.99	NULL	NULL
1003	45.48	5.00	43.206
1004	89.99	NULL	NULL
1005	9.99	0.00	9.99

Orders 1002 and 1004 have NULL discounts, causing their totals to be NULL.

-- With COALESCE: NULL discount treated as 0%
SELECT
    order_id,
    total_amount,
    COALESCE(discount_pct, 0) AS discount_pct,
    ROUND(total_amount * (1 - COALESCE(discount_pct, 0) / 100), 2) AS final_total
FROM orders;

order_id	total_amount	discount_pct	final_total
1001	155.97	10.00	140.37
1002	299.99	0.00	299.99
1003	45.48	5.00	43.21
1004	89.99	0.00	89.99
1005	9.99	0.00	9.99

Every row now has a valid total.

COALESCE in Aggregation

-- SUM skips NULLs silently
SELECT SUM(loyalty_points) AS total_points FROM customers;
-- Result: 350 (only counts 250 + 100 + 0, skips NULLs for Bob and Eve)

-- Use COALESCE to treat NULL as 0
SELECT SUM(COALESCE(loyalty_points, 0)) AS total_points FROM customers;
-- Result: 350 (same in this case because SUM already ignores NULLs)

-- But AVG is different!
SELECT AVG(loyalty_points) AS avg_points FROM customers;
-- Result: 116.67 (350 / 3, only counts non-NULL rows)

SELECT AVG(COALESCE(loyalty_points, 0)) AS avg_points FROM customers;
-- Result: 70.00 (350 / 5, counts all rows)

warning

AVG and COUNT(column) silently skip NULL values, which changes the denominator. If Bob and Eve genuinely have zero points (not unknown), use COALESCE to include them. If their points are truly unknown, skipping them may be correct. The business context determines the right approach.

-- COUNT(*) counts all rows: 5
-- COUNT(loyalty_points) counts non-NULL only: 3
SELECT
    COUNT(*) AS all_rows,
    COUNT(loyalty_points) AS non_null_rows
FROM customers;

COALESCE in String Concatenation

-- PostgreSQL: || returns NULL if any operand is NULL
SELECT first_name || ' ' || last_name || ' (' || city || ')' AS display
FROM customers;

display
Alice Martin (New York)
Bob Jones (Chicago)
NULL
Dave Wilson (Boston)
Eve Turner (Denver)

Carol's entire string is NULL because her city is NULL.

-- Fix with COALESCE
SELECT
    first_name || ' ' || last_name ||
    ' (' || COALESCE(city, 'Location unknown') || ')' AS display
FROM customers;

display
Alice Martin (New York)
Bob Jones (Chicago)
Carol Smith (Location unknown)
Dave Wilson (Boston)
Eve Turner (Denver)

NULLIF: Creating NULLs Strategically

NULLIF does the opposite of COALESCE. Instead of replacing NULL with a value, it creates NULL when two values are equal.

Syntax

NULLIF(expression1, expression2)

Returns NULL if expression1 = expression2. Otherwise returns expression1.

SELECT
    NULLIF(10, 10) AS same_values,      -- NULL
    NULLIF(10, 20) AS diff_values,      -- 10
    NULLIF('abc', 'abc') AS same_str,   -- NULL
    NULLIF('abc', 'xyz') AS diff_str;   -- 'abc'

same_values	diff_values	same_str	diff_str
NULL	10	NULL	abc

The Primary Use: Preventing Division by Zero

This is by far the most common reason to use NULLIF:

-- Without NULLIF: crashes on division by zero
SELECT product_name, price / stock_qty AS price_per_unit
FROM products;
-- ERROR: division by zero (Mechanical Keyboard has stock_qty = 0)

-- With NULLIF: returns NULL instead of crashing
SELECT
    product_name,
    stock_qty,
    price / NULLIF(stock_qty, 0) AS price_per_unit
FROM products;

product_name	stock_qty	price_per_unit
Wireless Mouse	150	0.17
USB-C Cable	300	0.03
Notebook A5	500	0.01
Mechanical Keyboard	0	NULL
Desk Lamp	3	11.50
Standing Desk	12	25.00

NULLIF(stock_qty, 0) turns zero into NULL. Dividing by NULL returns NULL instead of an error. You can then wrap the whole expression in COALESCE to show a default:

SELECT
    product_name,
    COALESCE(price / NULLIF(stock_qty, 0), 0) AS price_per_unit
FROM products;

product_name	price_per_unit
Wireless Mouse	0.17
USB-C Cable	0.03
Notebook A5	0.01
Mechanical Keyboard	0
Desk Lamp	11.50
Standing Desk	25.00

Now the Mechanical Keyboard shows 0 instead of NULL.

Treating Empty Strings as NULL

Imported data often has empty strings ('') where NULL would be more appropriate:

-- The Desk Lamp has description = '' (empty string, not NULL)
-- The Notebook has description = NULL
SELECT
    product_name,
    description,
    NULLIF(description, '') AS cleaned_description
FROM products;

product_name	description	cleaned_description
Wireless Mouse	Ergonomic wireless mouse	Ergonomic wireless mouse
USB-C Cable	Braided 2m cable	Braided 2m cable
Notebook A5	NULL	NULL
Desk Lamp	NULL	NULL
Standing Desk	Height-adjustable standing desk	Height-adjustable standing desk

The Desk Lamp's empty string becomes NULL, making it consistent with the Notebook's actual NULL. This is useful before applying COALESCE:

SELECT
    product_name,
    COALESCE(NULLIF(description, ''), 'No description available') AS description
FROM products;

product_name	description
Wireless Mouse	Ergonomic wireless mouse
USB-C Cable	Braided 2m cable
Notebook A5	No description available
Mechanical Keyboard	Cherry MX switches
Desk Lamp	No description available
Standing Desk	Height-adjustable standing desk

Both empty strings and NULLs are now replaced with the fallback text.

tip

The pattern COALESCE(NULLIF(column, ''), default) is essential for handling messy data where both empty strings and NULLs represent "no value." It normalizes both cases into a single fallback.

NULLIF for Conditional Suppression

Suppress a value that should not be displayed:

-- Show discount only if it's greater than 0
SELECT
    order_id,
    discount_pct,
    NULLIF(discount_pct, 0) AS meaningful_discount
FROM orders;

order_id	discount_pct	meaningful_discount
1001	10.00	10.00
1002	NULL	NULL
1003	5.00	5.00
1004	NULL	NULL
1005	0.00	NULL

Order 1005 had discount_pct = 0.00, which NULLIF converts to NULL, distinguishing "no discount applied" from "discount unknown."

IFNULL and ISNULL: Database-Specific Alternatives

COALESCE is the SQL standard and works everywhere, but several databases offer their own two-argument shortcuts.

MySQL: IFNULL

-- MySQL: IFNULL(expression, default_value)
SELECT
    first_name,
    IFNULL(phone, 'No phone') AS phone,
    IFNULL(loyalty_points, 0) AS points
FROM customers;

note

IFNULL(a, b) is equivalent to COALESCE(a, b) but accepts exactly two arguments.

SQL Server: ISNULL

-- SQL Server: ISNULL(expression, default_value)
SELECT
    first_name,
    ISNULL(phone, 'No phone') AS phone,
    ISNULL(loyalty_points, 0) AS points
FROM customers;

Differences from COALESCE

Feature	`COALESCE`	`IFNULL` (MySQL)	`ISNULL` (SQL Server)
SQL Standard	Yes	No	No
Number of arguments	Unlimited	Exactly 2	Exactly 2
Return type	Determined by all arguments	Determined by first argument	Determined by first argument
Available in	All databases	MySQL only	SQL Server only

warning

ISNULL in SQL Server has a subtle type behavior that differs from COALESCE. It uses the data type of the first argument for the return type, which can silently truncate data:

-- SQL Server: ISNULL truncates to the first argument's type
DECLARE @short VARCHAR(5) = NULL;
SELECT ISNULL(@short, 'This is a long string');
-- Result: 'This ' (truncated to 5 characters!)

SELECT COALESCE(@short, 'This is a long string');
-- Result: 'This is a long string' (full string)

Always prefer COALESCE over ISNULL in SQL Server to avoid this truncation trap.

Oracle: NVL

Oracle's equivalent is NVL:

-- Oracle: NVL(expression, default_value)
SELECT
    first_name,
    NVL(phone, 'No phone') AS phone,
    NVL(loyalty_points, 0) AS points
FROM customers;

Oracle also provides NVL2, which returns one value if the expression is not NULL and a different value if it is:

-- Oracle: NVL2(expression, value_if_not_null, value_if_null)
SELECT
    first_name,
    NVL2(email, 'Has email', 'No email') AS email_status
FROM customers;

Summary: Which Function to Use

Database	Two-Argument Shortcut	Recommended
PostgreSQL	None (use `COALESCE`)	`COALESCE`
MySQL	`IFNULL`	`COALESCE` (standard, more flexible)
SQL Server	`ISNULL`	`COALESCE` (avoids truncation)
Oracle	`NVL`	`COALESCE` (standard) or `NVL`
SQLite	`IFNULL`	`COALESCE`

tip

Use COALESCE everywhere. It is the SQL standard, works in every database, supports unlimited arguments, and avoids the truncation issues of ISNULL. The database-specific functions exist for historical reasons, but there is no advantage to using them in new code.

IS NULL and IS NOT NULL: The Only Safe NULL Comparisons

You cannot use = or != to compare with NULL. You must use IS NULL and IS NOT NULL.

-- Wrong: these conditions NEVER match NULL rows
SELECT * FROM customers WHERE phone = NULL;     -- 0 rows (always)
SELECT * FROM customers WHERE phone != NULL;    -- 0 rows (always)

-- Correct
SELECT * FROM customers WHERE phone IS NULL;    -- Bob, Eve
SELECT * FROM customers WHERE phone IS NOT NULL; -- Alice, Carol, Dave

Common Pattern: Finding Incomplete Records

-- Customers missing any contact information
SELECT
    customer_id,
    first_name,
    email,
    phone
FROM customers
WHERE email IS NULL
   OR phone IS NULL;

customer_id	first_name	email	phone
2	Bob	bob@example.com	NULL
3	Carol	NULL	555-0103
5	Eve	NULL	NULL

Common Pattern: Finding Unshipped Orders

SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date IS NULL;

order_id	order_date	ship_date
1003	2024-06-05	NULL
1004	2024-06-10	NULL
1005	2024-06-14	NULL

NULL-Safe Strategies

Strategy 1: Default NULLs at Query Time

Use COALESCE in your SELECT to present clean data without modifying the underlying table:

SELECT
    product_name,
    COALESCE(sale_price, price) AS display_price,
    COALESCE(NULLIF(description, ''), 'No description') AS display_description,
    COALESCE(stock_qty, 0) AS display_stock
FROM products;

Strategy 2: Prevent NULLs at the Schema Level

The best NULL strategy is often to prevent them from entering the database in the first place:

CREATE TABLE customers (
    customer_id    INT PRIMARY KEY,
    first_name     VARCHAR(50) NOT NULL,
    last_name      VARCHAR(50) NOT NULL,
    email          VARCHAR(100),                                -- nullable: email is optional
    loyalty_points INT NOT NULL DEFAULT 0,                      -- never NULL: use 0 instead
    city           VARCHAR(50) NOT NULL DEFAULT 'Unknown',      -- never NULL
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Use NOT NULL DEFAULT value for columns where "no data" has a meaningful zero or default. Leave columns nullable only when NULL genuinely means "unknown" or "not applicable."

Strategy 3: Normalize NULLs on Insert/Update

Clean data as it enters the system:

-- Normalize empty strings to NULL on insert
INSERT INTO customers (customer_id, first_name, last_name, email, phone)
VALUES (6, 'Frank', 'Lee', NULLIF(TRIM(''), ''), NULLIF(TRIM('  '), ''));
-- Both empty/whitespace-only strings become NULL

-- Clean existing data
UPDATE products
SET description = NULLIF(TRIM(description), '')
WHERE description = '' OR description = ' ';

Strategy 4: COALESCE in JOINs

NULL foreign keys do not match in standard joins. Handle them explicitly:

-- This misses customers with NULL city values in a join
SELECT c.first_name, r.region_name
FROM customers c
JOIN regions r ON c.city = r.city;
-- Customers with city = NULL are excluded

-- Include them with COALESCE
SELECT c.first_name, COALESCE(r.region_name, 'Unassigned') AS region
FROM customers c
LEFT JOIN regions r ON c.city = r.city;

Strategy 5: NULL-Safe Equality (Database-Specific)

Some databases offer operators that treat NULL = NULL as TRUE:

-- PostgreSQL: IS NOT DISTINCT FROM
SELECT * FROM customers c1
JOIN customers c2 ON c1.city IS NOT DISTINCT FROM c2.city;
-- Matches rows even when both cities are NULL

-- MySQL: <=> (NULL-safe equals)
SELECT * FROM customers c1
JOIN customers c2 ON c1.city <=> c2.city;
-- Same behavior

-- Standard SQL equivalent (all databases)
SELECT * FROM customers c1
JOIN customers c2 ON c1.city = c2.city
   OR (c1.city IS NULL AND c2.city IS NULL);

COALESCE with CASE: Powerful Combinations

COALESCE and CASE work together naturally:

-- Complex fallback logic
SELECT
    order_id,
    COALESCE(
        CASE WHEN ship_date IS NOT NULL THEN 'Shipped on ' || ship_date::TEXT END,
        CASE WHEN notes IS NOT NULL AND notes != '' THEN 'Note: ' || notes END,
        'No updates available'
    ) AS status_info
FROM orders;

order_id	status_info
1001	Shipped on 2024-01-18
1002	Shipped on 2024-03-25
1003	Note: Fragile items
1004	No updates available
1005	No updates available

info

This pattern uses CASE expressions that return NULL when their condition fails (no ELSE clause), and COALESCE picks the first non-NULL result. It creates a priority chain: show shipping info if available, then notes, then a generic message.

Common Mistakes

Mistake 1: Using = Instead of IS NULL

-- Bug: returns 0 rows even when NULLs exist
SELECT * FROM customers WHERE email = NULL;

-- Correct
SELECT * FROM customers WHERE email IS NULL;

This is the most fundamental NULL mistake and the first one every developer should memorize.

Mistake 2: NOT IN with NULLs

NOT IN silently fails when the subquery contains NULL:

-- Suppose the subquery returns (1, 2, NULL)
SELECT * FROM customers
WHERE customer_id NOT IN (1, 2, NULL);
-- Returns 0 rows! Always!

Why? customer_id NOT IN (1, 2, NULL) means customer_id != 1 AND customer_id != 2 AND customer_id != NULL. The last condition is always UNKNOWN, making the entire AND chain UNKNOWN.

Fix: Use NOT EXISTS or filter NULLs from the subquery:

-- Safe: NOT EXISTS handles NULLs correctly
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Also safe: filter NULLs out of NOT IN
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

danger

NOT IN with a subquery that can return NULL is one of the most dangerous patterns in SQL. The query runs without error but returns wrong results. Always prefer NOT EXISTS over NOT IN when checking for absence.

Mistake 3: Forgetting That Aggregates Skip NULLs

-- How many customers have loyalty points?
SELECT
    COUNT(*) AS total_customers,              -- 5
    COUNT(loyalty_points) AS with_points,     -- 3 (skips NULLs)
    AVG(loyalty_points) AS avg_points,        -- 116.67 (350/3, not 350/5)
    SUM(loyalty_points) AS total_points       -- 350
FROM customers;

total_customers	with_points	avg_points	total_points
5	3	116.67	350

If NULL should be treated as zero:

SELECT
    COUNT(*) AS total_customers,
    AVG(COALESCE(loyalty_points, 0)) AS avg_points,   -- 70.00 (350/5)
    SUM(COALESCE(loyalty_points, 0)) AS total_points  -- 350
FROM customers;

total_customers	avg_points	total_points
5	70.00	350

Mistake 4: ORDER BY with NULLs

NULLs sort to the beginning or end depending on the database:

Database	NULLs in ASC	NULLs in DESC
PostgreSQL	Last	First
MySQL	First	Last
SQL Server	First	Last
Oracle	Last	First

SELECT first_name, loyalty_points
FROM customers
ORDER BY loyalty_points ASC;
-- Where do Bob (NULL) and Eve (NULL) appear? Depends on your database!

Control NULL sort position explicitly:

-- PostgreSQL / Oracle: NULLS FIRST or NULLS LAST
SELECT first_name, loyalty_points
FROM customers
ORDER BY loyalty_points ASC NULLS LAST;

-- All databases: use COALESCE to control sort
SELECT first_name, loyalty_points
FROM customers
ORDER BY COALESCE(loyalty_points, -1) ASC;
-- NULLs treated as -1, sorting them before 0

Mistake 5: Confusing Empty String with NULL

-- These are DIFFERENT values
SELECT
    '' IS NULL AS empty_is_null,         -- FALSE (in most databases)
    NULL IS NULL AS null_is_null,        -- TRUE
    LENGTH('') AS empty_length,          -- 0
    LENGTH(NULL) AS null_length;         -- NULL

empty_is_null	null_is_null	empty_length	null_length
FALSE	TRUE	0	NULL

info

Oracle is the exception: In Oracle, an empty string '' IS treated as NULL. This is Oracle-specific behavior that differs from every other database.

-- Oracle only:
SELECT '' IS NULL FROM DUAL;  -- Result: TRUE
SELECT LENGTH('') FROM DUAL;  -- Result: NULL

If you are writing cross-database code, never rely on empty string behavior. Use NULLIF(column, '') to normalize both cases.

Mistake 6: COALESCE with Mismatched Types

All arguments to COALESCE should be the same type or compatible types:

-- Error in strict databases: mixing integer and string
SELECT COALESCE(loyalty_points, 'N/A') FROM customers;
-- ERROR: COALESCE types integer and text cannot be matched

Fix: Cast to a common type:

SELECT COALESCE(CAST(loyalty_points AS VARCHAR), 'N/A') FROM customers;
-- Or for display: keep the number numeric, handle NULL separately
SELECT COALESCE(loyalty_points, 0) FROM customers;

Comprehensive NULL Handling Cheat Sheet

Task	Solution	Example
Replace NULL with default	`COALESCE(col, default)`	`COALESCE(phone, 'N/A')`
Multi-level fallback	`COALESCE(a, b, c)`	`COALESCE(mobile, home, work)`
Safe division	`a / NULLIF(b, 0)`	`price / NULLIF(qty, 0)`
Empty string as NULL	`NULLIF(col, '')`	`NULLIF(description, '')`
Clean + default	`COALESCE(NULLIF(col,''), default)`	`COALESCE(NULLIF(notes,''), 'None')`
Check for NULL	`col IS NULL`	`WHERE email IS NULL`
Check for non-NULL	`col IS NOT NULL`	`WHERE phone IS NOT NULL`
NULL-safe compare	`IS NOT DISTINCT FROM` (PG)	`a IS NOT DISTINCT FROM b`
Count including NULLs	`COUNT(*)`	`COUNT(*)` counts all rows
Count excluding NULLs	`COUNT(column)`	`COUNT(email)` skips NULLs
AVG treating NULL as 0	`AVG(COALESCE(col, 0))`	`AVG(COALESCE(points, 0))`
Sort NULLs last	`ORDER BY col NULLS LAST`	PostgreSQL / Oracle
Sort NULLs via COALESCE	`ORDER BY COALESCE(col, val)`	All databases

Summary

SQL COALESCE is the standard function for replacing NULL values with meaningful defaults. Together with NULLIF, IS NULL, and database-specific functions like IFNULL and ISNULL, it forms the toolkit for handling one of SQL's most challenging concepts.

Key takeaways:

NULL means unknown, not zero, not empty string. It behaves unlike any other value: comparisons with NULL return UNKNOWN, arithmetic with NULL returns NULL, and most functions receiving NULL return NULL.
COALESCE(a, b, c, ...) returns the first non-NULL argument. It is the SQL standard, works in every database, and should be your go-to function for NULL replacement. Use it for default values, fallback chains, safe calculations, and clean display output.
NULLIF(a, b) returns NULL when a = b. Its primary use is preventing division by zero (x / NULLIF(divisor, 0)) and converting empty strings to NULL (NULLIF(column, '')).
IFNULL (MySQL/SQLite), ISNULL (SQL Server), and NVL (Oracle) are database-specific two-argument shortcuts. Prefer COALESCE for portability and to avoid SQL Server's ISNULL truncation behavior.
Use IS NULL and IS NOT NULL for comparisons, never = NULL or != NULL.
Beware of NOT IN with NULLs, which silently returns no rows. Use NOT EXISTS instead.
Aggregates skip NULLs silently. COUNT(column) counts only non-NULL values. AVG divides by the count of non-NULL values only. Use COALESCE to include NULLs in aggregations when appropriate.
Prevent NULLs at the schema level with NOT NULL DEFAULT value for columns where NULL is not a meaningful state.
The pattern COALESCE(NULLIF(column, ''), 'default') handles both empty strings and NULLs, normalizing messy imported data into clean, consistent output.

Handle NULL deliberately and explicitly. Every column you query should have a conscious strategy: either prevent NULL at the schema level, replace it with COALESCE at query time, or accept it as a valid "unknown" state with appropriate IS NULL checks.

Why NULL Is Different from Everything Else​

The Sample Data​

COALESCE: The Essential NULL Replacement​

Syntax​

Basic Examples​

Providing Default Values for NULL Columns​

Fallback Chain: Multiple Alternatives​

COALESCE in Calculations​

COALESCE in Aggregation​

COALESCE in String Concatenation​

NULLIF: Creating NULLs Strategically​

Syntax​

The Primary Use: Preventing Division by Zero​

Treating Empty Strings as NULL​

NULLIF for Conditional Suppression​

IFNULL and ISNULL: Database-Specific Alternatives​

MySQL: IFNULL​

SQL Server: ISNULL​

Differences from COALESCE​

Oracle: NVL​

Summary: Which Function to Use​

IS NULL and IS NOT NULL: The Only Safe NULL Comparisons​

Common Pattern: Finding Incomplete Records​

Common Pattern: Finding Unshipped Orders​

NULL-Safe Strategies​

Strategy 1: Default NULLs at Query Time​

Strategy 2: Prevent NULLs at the Schema Level​

Strategy 3: Normalize NULLs on Insert/Update​

Strategy 4: COALESCE in JOINs​

Strategy 5: NULL-Safe Equality (Database-Specific)​

COALESCE with CASE: Powerful Combinations​

Common Mistakes​

Mistake 1: Using = Instead of IS NULL​

Mistake 2: NOT IN with NULLs​

Mistake 3: Forgetting That Aggregates Skip NULLs​

Mistake 4: ORDER BY with NULLs​

Mistake 5: Confusing Empty String with NULL​

Mistake 6: COALESCE with Mismatched Types​

Comprehensive NULL Handling Cheat Sheet​

Summary​

Table of Contents

Why NULL Is Different from Everything Else

The Sample Data

COALESCE: The Essential NULL Replacement

Syntax

Basic Examples

Providing Default Values for NULL Columns

Fallback Chain: Multiple Alternatives

COALESCE in Calculations

COALESCE in Aggregation

COALESCE in String Concatenation

NULLIF: Creating NULLs Strategically

Syntax

The Primary Use: Preventing Division by Zero

Treating Empty Strings as NULL

NULLIF for Conditional Suppression

IFNULL and ISNULL: Database-Specific Alternatives

MySQL: IFNULL

SQL Server: ISNULL

Differences from COALESCE

Oracle: NVL

Summary: Which Function to Use

IS NULL and IS NOT NULL: The Only Safe NULL Comparisons

Common Pattern: Finding Incomplete Records

Common Pattern: Finding Unshipped Orders

NULL-Safe Strategies

Strategy 1: Default NULLs at Query Time

Strategy 2: Prevent NULLs at the Schema Level

Strategy 3: Normalize NULLs on Insert/Update

Strategy 4: COALESCE in JOINs

Strategy 5: NULL-Safe Equality (Database-Specific)

COALESCE with CASE: Powerful Combinations

Common Mistakes

Mistake 1: Using = Instead of IS NULL

Mistake 2: NOT IN with NULLs

Mistake 3: Forgetting That Aggregates Skip NULLs

Mistake 4: ORDER BY with NULLs

Mistake 5: Confusing Empty String with NULL

Mistake 6: COALESCE with Mismatched Types

Comprehensive NULL Handling Cheat Sheet

Summary