SQL CASE, COALESCE, and NULL Handling

NULL is not zero and not empty string

NULL in SQL means unknown. It is not zero, it is not an empty string, and it does not equal anything — not even another NULL. Any arithmetic or comparison involving NULL returns NULL (or UNKNOWN for boolean expressions). Every SQL developer runs into this eventually; understanding it up front saves hours of debugging.

SELECT NULL = NULL;    -- UNKNOWN (not TRUE)
SELECT NULL + 100;     -- NULL
SELECT NULL || 'hello'; -- NULL (Postgres) or 'hello' (MySQL CONCAT)
SELECT COALESCE(NULL, 'fallback');  -- 'fallback'

CASE — SQL's if/else

CASE returns a value based on a condition. It appears in SELECT, WHERE, ORDER BY, and inside aggregate functions.

-- Label orders by size tier
SELECT
    id,
    customer_id,
    total_amount,
    CASE
        WHEN total_amount >= 500 THEN 'Large'
        WHEN total_amount >= 100 THEN 'Medium'
        WHEN total_amount >    0 THEN 'Small'
        ELSE 'Free'
    END AS order_tier
FROM orders;

-- Map a status code to a human-readable label (simple CASE form)
SELECT
    id,
    CASE status
        WHEN 'pending'   THEN 'Awaiting payment'
        WHEN 'shipped'   THEN 'On its way'
        WHEN 'delivered' THEN 'Delivered'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE 'Unknown status: ' || status
    END AS status_label
FROM orders;

CASE for custom sort order

-- Prioritise urgent tickets; within each priority, oldest first
SELECT id, title, priority, created_at
FROM   support_tickets
WHERE  status = 'open'
ORDER  BY CASE priority
            WHEN 'critical' THEN 1
            WHEN 'high'     THEN 2
            WHEN 'medium'   THEN 3
            ELSE 4
          END,
          created_at ASC;

COALESCE — the NULL default

COALESCE(a, b, c, ...) returns the first non-NULL argument. It is the standard way to supply a fallback when a column might be NULL.

-- Show 'N/A' when phone is not provided
SELECT
    customer_id,
    COALESCE(phone, 'N/A')           AS phone,
    COALESCE(discount, 0)            AS discount,
    COALESCE(nickname, first_name)   AS display_name
FROM customers;

-- Prevent NULL in arithmetic (NULL * quantity = NULL for the whole line)
SELECT
    product_id,
    unit_price * COALESCE(quantity, 0) AS line_total
FROM cart_items;

-- Pick the first available contact method
SELECT
    COALESCE(preferred_email, work_email, personal_email, 'no-email') AS contact
FROM employees;

NULLIF — converting a specific value to NULL

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It is most useful to prevent division-by-zero errors.

-- Conversion rate: avoid dividing by zero if no impressions
SELECT
    campaign_id,
    clicks,
    impressions,
    ROUND(100.0 * clicks / NULLIF(impressions, 0), 2) AS ctr_pct
FROM ad_campaigns;

-- Treat empty strings the same as NULL
SELECT COALESCE(NULLIF(TRIM(notes), ''), 'No notes') AS notes
FROM support_tickets;
-- Empty or whitespace-only notes → 'No notes'

Conditional aggregation — pivoting data

Combining CASE with an aggregate function computes multiple metrics in a single pass — no subqueries or UNION needed.

-- Monthly orders broken down by status in one query
SELECT
    DATE_TRUNC('month', created_at)                             AS month,
    COUNT(*)                                                    AS total,
    COUNT(CASE WHEN status = 'completed' THEN 1 END)            AS completed,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END)            AS cancelled,
    COUNT(CASE WHEN status = 'refunded'  THEN 1 END)            AS refunded,
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Postgres shorthand with FILTER:

COUNT(*) FILTER (WHERE status = 'completed') AS completed

NOT IN with NULLs — the silent zero-rows trap

-- Suppose discontinued_products has a NULL product_id row
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued_products);
-- Returns ZERO rows if any product_id in the subquery is NULL
-- Because: 1 <> NULL is UNKNOWN → NOT IN returns no rows at all

-- Safe alternative: NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued_products d WHERE d.product_id = p.id
);
-- Correctly returns all non-discontinued products

IS DISTINCT FROM — NULL-safe equality

Standard = returns UNKNOWN when either side is NULL. Use IS NOT DISTINCT FROM (Postgres) for NULL-safe equality:

-- Find rows where the shipping address changed (may be NULL on either side)
SELECT order_id
FROM order_history
WHERE old_shipping_address IS DISTINCT FROM new_shipping_address;
-- Returns rows where values genuinely differ, including NULL vs non-NULL changes
-- NULL IS DISTINCT FROM NULL → FALSE (they are the same)

GREATEST and LEAST

-- Clamp a user-supplied rating to the valid 1–5 range
SELECT
    user_id,
    raw_rating,
    LEAST(GREATEST(raw_rating, 1), 5) AS clamped_rating
FROM feedback;

-- Find the later of two dates
SELECT
    order_id,
    GREATEST(shipped_at, returned_at) AS last_event
FROM orders;

Recap

NULL is not zero — test for it with IS NULL / IS NOT NULL, never with = NULL. Use COALESCE to provide defaults; NULLIF to convert a sentinel value to NULL (especially before division). Use CASE inside aggregate functions for in-query pivoting. Never use NOT IN (subquery) when the subquery might return NULLs — use NOT EXISTS instead. IS NOT DISTINCT FROM is the NULL-safe equality test for upsert and change-detection queries.

SQL CASE, COALESCE, and NULL Handling — Practical Patterns