NULL Tests, COALESCE, NULLIF, and Safe Defaults

Learning outcomes

NULL represents missing or inapplicable information. A safe SQL expression does not erase that meaning accidentally, but it can provide an explicit fallback where the result contract requires one.

NULL propagates through many expressions

SELECT    10 + NULL AS arithmetic_result,    'email: ' || NULL AS concatenation_result,    LOWER(NULL) AS function_result,    NULL = NULL AS equality_result,    NULL IS NULL AS null_test_result;

Ordinary equality does not answer whether a value is NULL. Use IS NULL or IS NOT NULL.

NULL tests preserve meaning

SELECT customer_id, full_name, emailFROM customerWHERE email IS NULLORDER BY customer_id;

SELECT customer_id, full_name, emailFROM customerWHERE email = ''ORDER BY customer_id;

The dataset intentionally contains both NULL and an empty string. They can represent different states and should not be merged without a business rule.

COALESCE chooses the first non-NULL expression

SELECT    customer_id,    COALESCE(city, '[city unknown]') AS city_label,    COALESCE(NULLIF(TRIM(email), ''), '[no email]') AS email_label,    COALESCE(credit_limit, 0) AS displayed_credit_limitFROM customerORDER BY customer_id;

COALESCE(a,b,c) checks arguments from left to right and returns the first non-NULL result. The chosen fallback becomes part of the output contract.

Zero is not a universal safe default

SELECT    customer_id,    COALESCE(credit_limit, 0) AS credit_limitFROM customer;

This may be correct for a display or a specific decision rule, but it also makes an unknown limit indistinguishable from a known limit of zero. A clearer report can expose both value and state.

SELECT    customer_id,    credit_limit,    COALESCE(credit_limit, 0) AS calculation_limit,    credit_limit IS NULL AS limit_was_missingFROM customerORDER BY customer_id;

NULLIF returns NULL when two expressions are equal

SELECT    customer_id,    NULLIF(TRIM(email), '') AS normalized_emailFROM customerORDER BY customer_id;

SELECT    product_id,    unit_price,    stock_qty,    unit_price / NULLIF(stock_qty, 0) AS price_per_stock_unitFROM productORDER BY product_id;

If stock_qty is zero, NULLIF(stock_qty,0) returns NULL. The division then yields NULL instead of attempting a zero divisor. This is safer than fabricating a numeric answer.

COALESCE and NULLIF compose well

SELECT    customer_id,    COALESCE(        LOWER(NULLIF(TRIM(email), '')),        '[no usable email]'    ) AS contact_emailFROM customerORDER BY customer_id;

Evaluation proceeds inside out: trim, convert empty text to NULL, lowercase a usable value, then provide a display fallback.

NULL-aware calculations

SELECT    s.sale_id,    p.unit_price,    s.quantity,    s.discount_rate,    ROUND(p.unit_price * s.quantity, 2) AS gross_amount,    ROUND(        p.unit_price * s.quantity * COALESCE(s.discount_rate, 0),        2    ) AS discount_amountFROM sale AS sJOIN product AS p ON p.product_id = s.product_idORDER BY s.sale_id;

Fallback types differ across engines

SQL engines determine a common result type for COALESCE and CASE according to their own type-resolution rules. Avoid mixing unrelated types merely because one branch is usually NULL.

SELECT    COALESCE(credit_limit, CAST(0 AS REAL)) AS effective_limitFROM customer;

-- This may coerce differently or fail across engines.SELECT COALESCE(credit_limit, 'unknown')FROM customer;

Practice lab

1. List customers with a missing city.
2. Normalize NULL, empty, and whitespace-only emails into NULL.
3. Display a fallback email label without modifying storage.
4. Calculate sale discounts by treating a missing discount rate as zero.
5. Calculate price per stock unit without dividing by zero.

SELECT customer_id, full_nameFROM customerWHERE city IS NULL;SELECT customer_id, NULLIF(TRIM(email), '') AS normalized_emailFROM customer;SELECT customer_id,       COALESCE(NULLIF(TRIM(email), ''), '[no email]') AS email_labelFROM customer;SELECT sale_id,       COALESCE(discount_rate, 0) AS effective_discount_rateFROM sale;SELECT product_id,       unit_price / NULLIF(stock_qty, 0) AS price_per_stock_unitFROM product;

Checkpoint

Common failures

Writing = NULL

The result is UNKNOWN rather than a successful null test.

Replacing every NULL with zero or empty text

This destroys distinctions that may matter to the business.

Using a sentinel instead of NULL

Values such as -1 or 'N/A' can collide with real domains and complicate constraints.

Mixing incompatible fallback types

Type resolution differs by engine and can produce errors or surprising coercion.

Hiding data-quality problems in presentation

A fallback label can make a report readable while the source still needs correction.

Summary and references

• Use IS NULL and IS NOT NULL for missingness.
• COALESCE returns the first non-NULL candidate.
• NULLIF converts a specific equal value into NULL.
• Defaults must have an explicit business or presentation meaning.
• Expose missingness separately when a fallback could hide important state.

References

• SQLite: COALESCE and NULLIF
• SQLite: SQL expressions and NULL behavior
• PostgreSQL: Conditional Expressions
• Microsoft Learn: COALESCE