CASE Expressions

TL;DR

CASE is SQL’s if/elif/else. It’s an expression (returns a value), not a statement, so it goes anywhere a value is allowed: SELECT, WHERE, ORDER BY, GROUP BY, the inside of an aggregate.

Two syntactic flavors, one semantic:

-- Searched CASE: arbitrary predicates, the most common form
CASE WHEN total > 100 THEN 'big'
     WHEN total > 10  THEN 'medium'
     ELSE 'small'
END

-- Simple CASE: equality only against one expression
CASE country
     WHEN 'US' THEN 'domestic'
     WHEN 'CA' THEN 'domestic'
     ELSE 'international'
END

Branches are evaluated top-to-bottom, first match wins. If no WHEN matches and no ELSE, the result is NULL.

A worked example

CREATE TABLE orders (
    id int, total numeric, country text, placed date
);
INSERT INTO orders VALUES
  (1,  5.00, 'US', '2026-04-01'),
  (2, 25.00, 'US', '2026-04-15'),
  (3, 99.00, 'UK', '2026-04-20'),
  (4, 150.00,'IN', '2026-04-22'),
  (5, NULL, 'UK', '2026-04-25');

CASE in SELECT — bucketing

SELECT id, total,
       CASE WHEN total IS NULL  THEN 'unknown'
            WHEN total < 10     THEN 'small'
            WHEN total < 100    THEN 'medium'
            ELSE                     'large'
       END AS bucket
FROM   orders
ORDER  BY id;

+----+--------+---------+
| id | total  | bucket  |
+----+--------+---------+
| 1  |   5.00 | small   |
| 2  |  25.00 | medium  |
| 3  |  99.00 | medium  |
| 4  | 150.00 | large   |
| 5  |  NULL  | unknown |
+----+--------+---------+

Note: the NULL check comes first. If you wrote WHEN total < 10 first, the NULL row would match no branch (NULL comparisons are unknown) and fall through to ELSE 'large' — wrong. NULLs first is the discipline.

CASE inside an aggregate — conditional counts and sums

The most-used CASE pattern in production analytics:

SELECT country,
       COUNT(*)                                              AS n,
       SUM(CASE WHEN total > 50 THEN 1 ELSE 0 END)           AS big_orders,
       SUM(CASE WHEN total > 50 THEN total ELSE 0 END)       AS big_revenue,
       AVG(CASE WHEN total IS NOT NULL THEN total END)       AS avg_known
FROM   orders
GROUP  BY country;

The CASE turns “yes/no” into 1/0 so SUM becomes a count and AVG becomes a rate. This single pattern replaces probably 30% of subqueries beginners reach for.

Postgres / Snowflake / DuckDB shortcut. Use FILTER: COUNT(*) FILTER (WHERE total > 50) reads cleaner. CASE-style works in every dialect; FILTER doesn’t (e.g. MySQL).

CASE in WHERE — gate filters

-- Apply a different threshold per country
SELECT * FROM orders
WHERE  CASE country
            WHEN 'US' THEN total > 5
            WHEN 'IN' THEN total > 1
            ELSE           total > 10
       END;

A CASE returning a boolean works in WHERE. Often clearer than a chain of (country = 'US' AND total > 5) OR (country = 'IN' AND total > 1) OR …. Sometimes less clear — measure on a real query, pick the readable form.

CASE in ORDER BY — custom sort

-- Sort: domestic first, then by total descending
SELECT * FROM orders
ORDER  BY CASE WHEN country = 'US' THEN 0 ELSE 1 END,
          total DESC NULLS LAST;

Standard “pin certain values to the top” trick. The CASE returns 0 or 1, which sorts before the secondary key.

Pivot — turning rows into columns

CASE is how you pivot in standard SQL:

SELECT user_id,
       SUM(CASE WHEN event = 'view'     THEN 1 ELSE 0 END) AS views,
       SUM(CASE WHEN event = 'click'    THEN 1 ELSE 0 END) AS clicks,
       SUM(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM   events
GROUP  BY user_id;

This is the canonical “wide table from long table” pivot. Postgres has a crosstab extension; Snowflake and SQL Server have PIVOT; portable code uses CASE + SUM.

Common pitfalls

• NULL comparisons silently fall through. CASE WHEN x = 5 THEN … does not match when x IS NULL. Test IS NULL explicitly first if NULL rows need handling.
• Type unification. All branches must return compatible types. Mixing THEN 1 and THEN 'big' errors out. If you really need text-or-number, cast: THEN 1::text.
• Order matters. Branches are evaluated top-down. Putting a broad predicate first shadows narrower ones below.
• Forgetting ELSE. Without it, unmatched rows get NULL. If you later SUM the result, NULLs are skipped — usually fine, sometimes not. Always include an ELSE when in doubt.
• Searched vs simple confusion. CASE x WHEN NULL THEN … does not match NULL — it’s x = NULL under the hood. Use the searched form (CASE WHEN x IS NULL THEN …).

CASE vs COALESCE, NULLIF, IIF, IF

A handful of shortcuts:

COALESCE and NULLIF cover so many CASE-pattern situations that you’ll rarely write the longhand version. NULLIF(divisor, 0) is the canonical divide-by-zero guard.

Production patterns for ML

1. Categorical encoding inline. When prepping features without materializing a one-hot table:

SELECT user_id,
       CASE country WHEN 'US' THEN 1 ELSE 0 END AS is_us,
       CASE country WHEN 'UK' THEN 1 ELSE 0 END AS is_uk,
       CASE country WHEN 'IN' THEN 1 ELSE 0 END AS is_in
FROM   users;

For sparse one-hots over thousands of categories, materialize a long table and pivot. For a handful of buckets, inline CASE is faster and more readable than joining a lookup.

2. Label gating for noisy training data. When the label is derived from multiple signals, CASE lets you encode the rule in one place:

SELECT order_id,
       CASE
           WHEN refunded            THEN 0
           WHEN shipped IS NOT NULL THEN 1
           WHEN cancelled           THEN 0
           ELSE NULL                  -- ambiguous; drop later
       END AS conversion_label
FROM   orders;

The explicit NULL for ambiguous cases is the right move — downstream you filter WHERE conversion_label IS NOT NULL. Better than silently labeling ambiguous events as 0.

Resources

• PostgreSQL — CASE — postgresql.org/docs
• PostgreSQL documentation — postgresql.org/docs
• Mode Analytics — CASE — mode.com/sql-tutorial
• SQLBolt — lessons on conditionals — sqlbolt.com