Conditional Expressions — CASE, COALESCE, NULLIF and GREATEST

SQL’s conditional expressions let you embed branching logic directly inside queries — transforming, categorising, and cleaning data without a separate Python processing step. CASE WHEN is the SQL equivalent of Python’s if/elif/else, producing different output values based on conditions. COALESCE replaces NULL with a fallback value. NULLIF converts a specific value to NULL (the inverse of COALESCE). Together, these tools let you shape data for API responses entirely in SQL — computing labels like “today”, “this week”, “popular”, or substituting defaults for missing values before data even reaches your FastAPI application.

CASE WHEN — Conditional Values #

-- Searched CASE: evaluate conditions in order, return first matching result
SELECT
    id,
    title,
    view_count,
    CASE
        WHEN view_count >= 10000 THEN 'viral'
        WHEN view_count >= 1000  THEN 'popular'
        WHEN view_count >= 100   THEN 'moderate'
        ELSE 'low'
    END AS popularity_label
FROM posts;

-- Simple CASE: compare one expression to many values
SELECT
    id,
    status,
    CASE status
        WHEN 'published' THEN '✓ Live'
        WHEN 'draft'     THEN '✎ Draft'
        WHEN 'archived'  THEN '✗ Hidden'
        ELSE '? Unknown'
    END AS status_label
FROM posts;

-- CASE in WHERE clause
SELECT * FROM posts
WHERE CASE
    WHEN status = 'published' THEN TRUE
    WHEN status = 'draft' AND author_id = 1 THEN TRUE
    ELSE FALSE
END;

-- CASE in ORDER BY — custom sort order
SELECT id, title, status
FROM posts
ORDER BY
    CASE status
        WHEN 'published' THEN 1
        WHEN 'draft'     THEN 2
        WHEN 'archived'  THEN 3
        ELSE 4
    END,
    created_at DESC;

-- CASE in aggregate (conditional counting)
SELECT
    COUNT(CASE WHEN view_count >= 1000 THEN 1 END) AS popular_posts,
    COUNT(CASE WHEN view_count <  1000 THEN 1 END) AS low_traffic_posts
FROM posts;

COALESCE — Replace NULL with a Default #

-- COALESCE(val1, val2, ...) returns the first non-NULL value in the list
SELECT COALESCE(NULL, NULL, 'first non-null', 'ignored');   -- 'first non-null'
SELECT COALESCE(NULL, 42);                                   -- 42
SELECT COALESCE(42, 99);                                     -- 42 (first is not NULL)

-- Provide fallback for missing data
SELECT
    id,
    COALESCE(excerpt, LEFT(body, 200)) AS display_excerpt,
    COALESCE(avatar_url, '/static/default-avatar.png') AS avatar
FROM posts p
LEFT JOIN user_profiles up ON p.author_id = up.user_id;

-- Default for nullable aggregates
SELECT
    author_id,
    COALESCE(SUM(view_count), 0) AS total_views   -- 0 instead of NULL if no posts
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id;

-- COALESCE in WHERE — treat NULL same as a value
SELECT * FROM posts
WHERE COALESCE(deleted_at, 'infinity'::TIMESTAMPTZ) > NOW();

NULLIF — Convert a Value to NULL #

-- NULLIF(val, compare_value): returns NULL if val = compare_value, else val
-- This is the inverse of COALESCE

SELECT NULLIF(0, 0);       -- NULL  (0 equals the comparison value)
SELECT NULLIF(5, 0);       -- 5     (5 does not equal 0)
SELECT NULLIF('', '');     -- NULL  (empty string becomes NULL)
SELECT NULLIF('hello',''); -- 'hello'

-- Practical: prevent division by zero
SELECT
    post_id,
    total_reads,
    total_reads / NULLIF(total_impressions, 0) AS read_rate
    -- NULLIF(0, 0) = NULL, and NULL / anything = NULL
    -- Without NULLIF: division by zero raises an error
FROM post_analytics;

-- Clean empty strings: treat '' same as NULL
SELECT COALESCE(NULLIF(TRIM(bio), ''), 'No bio provided') AS display_bio
FROM user_profiles;
-- TRIM removes whitespace → NULLIF('', '') = NULL → COALESCE returns default

GREATEST and LEAST #

-- GREATEST: returns the largest value from a list
-- LEAST: returns the smallest value from a list
-- Both return NULL if ANY argument is NULL (unlike MAX/MIN which ignore NULLs)

SELECT GREATEST(1, 5, 3, 2);       -- 5
SELECT LEAST(1, 5, 3, 2);          -- 1
SELECT GREATEST('apple', 'banana', 'cherry');  -- 'cherry' (alphabetical)

-- Clamp a value to a range: max(min_val, min(max_val, val))
SELECT GREATEST(0, LEAST(100, user_score))   AS clamped_score
FROM users;
-- Ensures score is between 0 and 100 regardless of input

-- Ensure updated_at never goes backwards
UPDATE posts
SET updated_at = GREATEST(updated_at, NOW())
WHERE id = 42;

-- Cap page size between 1 and 100
SELECT GREATEST(1, LEAST(100, :page_size)) AS safe_page_size;

Common Mistakes #

Mistake 1 — Forgetting ELSE in CASE (silent NULLs) #

❌ Wrong — no ELSE means unmatched rows return NULL:

CASE WHEN status = 'published' THEN 'live'
     WHEN status = 'draft'     THEN 'editing'
END AS label
-- Archived posts return NULL label silently!

✅ Correct — always include ELSE:

CASE WHEN status = 'published' THEN 'live'
     WHEN status = 'draft'     THEN 'editing'
     ELSE 'other'
END AS label   -- ✓ no NULLs

Mistake 2 — Division by zero without NULLIF #

❌ Wrong — crashes when denominator is 0:

SELECT total_clicks / total_views AS ctr FROM ads;   -- ERROR if total_views = 0!

✅ Correct:

SELECT total_clicks::FLOAT / NULLIF(total_views, 0) AS ctr FROM ads;   -- NULL instead of error ✓

Mistake 3 — Using COALESCE to check multiple columns when only one is needed #

❌ Wrong — evaluates both regardless:

COALESCE(expensive_function(), other_expensive_function())
-- Both functions are called even if the first returns non-NULL

✅ Correct — COALESCE short-circuits in most cases; for truly expensive operations consider CASE WHEN.

Quick Reference #