Chapter 06 · Functions, NULLs, and Conditional Logic
CASE Expressions and Conditional Projection
Translate business rules into readable result columns without hiding precedence, overlap, or missing-value behavior.
Learning outcomes
A CASE expression returns one value according to ordered conditions. It is SQL's general conditional expression and can build labels, buckets, flags, and conditional sort keys.
Distinguish simple CASE from searched CASE.
Order overlapping conditions so the first matching branch is intentional.
Handle NULL explicitly inside conditional output.
Use CASE in projection and ORDER BY without turning rules into unreadable code.
CASE selects one result
CASE stops at the first branch that matches. Branch order is therefore part of the business rule.
SELECT product_id, product_name, stock_qty, CASE WHEN stock_qty = 0 THEN 'out of stock' WHEN stock_qty < 25 THEN 'low stock' WHEN stock_qty < 100 THEN 'normal stock' ELSE 'high stock' END AS stock_statusFROM productORDER BY product_id;Simple CASE compares one expression
SELECT sale_id, sales_channel, CASE sales_channel WHEN 'web' THEN 'Self-service web' WHEN 'partner' THEN 'Partner assisted' WHEN 'direct' THEN 'Direct sales team' ELSE 'Unclassified channel' END AS channel_labelFROM saleORDER BY sale_id;Simple CASE is concise when one expression is compared for equality against several values. It is not suited to ranges or unrelated predicates.
Searched CASE supports general predicates
SELECT product_id, product_name, unit_price, CASE WHEN unit_price < 25 THEN 'budget' WHEN unit_price < 70 THEN 'standard' ELSE 'premium' END AS price_bandFROM productORDER BY unit_price, product_id;Because the conditions are evaluated top to bottom, the second branch means “at least 25 and less than 70” after the first branch has failed.
| Form | Best use | Limitation |
|---|---|---|
| Simple CASE | Equality categories for one expression | Cannot directly express ranges or compound predicates. |
| Searched CASE | Ranges, compound rules, NULL tests | Can become difficult to read when branches are numerous. |
| Lookup table | Data-driven labels and policies | Requires a join and careful range/key modeling. |
Overlapping conditions require correct order
CASE WHEN unit_price >= 25 THEN 'standard or premium' WHEN unit_price >= 70 THEN 'premium' ELSE 'budget'ENDThe premium branch is unreachable because every value at least 70 already matched the first branch.
CASE WHEN unit_price >= 70 THEN 'premium' WHEN unit_price >= 25 THEN 'standard' ELSE 'budget'ENDELSE is part of the result contract
SELECT customer_id, city, CASE WHEN city IS NULL THEN 'unknown region' WHEN city IN ('Tehran', 'Berlin') THEN 'priority city' ELSE 'standard city' END AS city_groupFROM customerORDER BY customer_id;If ELSE is omitted and no branch matches, CASE returns NULL. That can be useful, but it should be intentional.
CASE and NULL
SELECT customer_id, CASE city WHEN NULL THEN 'missing' ELSE city END AS incorrect_city_labelFROM customer;SELECT customer_id, CASE WHEN city IS NULL THEN '[city unknown]' ELSE city END AS city_labelFROM customerORDER BY customer_id;Conditional projection can expose multiple signals
SELECT customer_id, credit_limit, CASE WHEN credit_limit IS NULL THEN 0 ELSE credit_limit END AS calculation_limit, CASE WHEN credit_limit IS NULL THEN 1 ELSE 0 END AS needs_limit_reviewFROM customerORDER BY customer_id;This is more transparent than silently replacing unknown values and discarding the state that caused the fallback.
CASE can define a conditional sort key
SELECT product_id, product_name, stock_qtyFROM productORDER BY CASE WHEN stock_qty = 0 THEN 0 ELSE 1 END, stock_qty ASC, product_id ASC;The first expression places out-of-stock products first. Subsequent keys define order within each group and provide a unique tie-breaker.
CASE inside calculations
SELECT sale_id, sales_channel, quantity, CASE WHEN sales_channel = 'partner' THEN quantity * 2.00 WHEN sales_channel = 'direct' THEN quantity * 1.00 ELSE 0.00 END AS service_feeFROM saleORDER BY sale_id;All result branches should represent compatible units and types. A result mixing money, labels, and booleans would be a poor contract even if one engine coerces it.
Conditional aggregation preview
SELECT SUM(CASE WHEN sales_channel = 'web' THEN 1 ELSE 0 END) AS web_sales, SUM(CASE WHEN sales_channel = 'partner' THEN 1 ELSE 0 END) AS partner_sales, SUM(CASE WHEN sales_channel = 'direct' THEN 1 ELSE 0 END) AS direct_salesFROM sale;This pattern is introduced here because it demonstrates conditional values. Aggregation and grouping are covered systematically in Chapter 8.
When CASE becomes too large
Keep CASE
A small, stable rule belongs close to the query result.
Use a lookup table
Labels or mappings change as data rather than code.
Use a range table
Price bands or thresholds need effective dates and administration.
Use application logic
The rule depends on interaction state or does not belong to data retrieval.
Do not duplicate the same complex business rule independently in dashboards, APIs, exports, and database queries. Choose one governed definition.
Practice lab
- Classify products as unavailable, low, or available by stock quantity.
- Label missing emails separately from blank emails.
- Create a price band with three non-overlapping ranges.
- Sort customers so missing cities appear last.
- Create a review flag for missing credit limits.
SELECT product_id, CASE WHEN stock_qty = 0 THEN 'unavailable' WHEN stock_qty < 25 THEN 'low' ELSE 'available' END AS stock_statusFROM product;SELECT customer_id, CASE WHEN email IS NULL THEN 'missing' WHEN TRIM(email) = '' THEN 'blank' ELSE 'present' END AS email_stateFROM customer;SELECT product_id, CASE WHEN unit_price < 25 THEN 'budget' WHEN unit_price < 70 THEN 'standard' ELSE 'premium' END AS price_bandFROM product;SELECT customer_id, cityFROM customerORDER BY CASE WHEN city IS NULL THEN 1 ELSE 0 END, city, customer_id;SELECT customer_id, CASE WHEN credit_limit IS NULL THEN 1 ELSE 0 END AS needs_reviewFROM customer;Common failures
Ordering branches incorrectly
A broad branch can shadow a narrower one.
Forgetting ELSE
Unmatched rows become NULL, sometimes without anyone noticing.
Matching NULL in simple CASE
Use a searched CASE with IS NULL.
Mixing incompatible result types
Vendor type-resolution rules can produce errors or unwanted coercion.
Embedding an ungoverned policy
A large CASE can become hidden business logic copied across systems.
Summary and references
- Simple CASE compares one input for equality.
- Searched CASE evaluates general predicates.
- The first matching WHEN branch wins.
- ELSE defines the unmatched result; without it, the result is NULL.
- CASE can create output columns, flags, calculations, and sort priorities.