🔀CASE expressions

Row-local branching: still an expression

CASE is not procedural “if/else” for batches: it is a scalar expression evaluated for each row where it appears (SELECT, ORDER BY, GROUP BY keys in some engines, UPDATE assignments, check constraints where allowed). Searched CASE evaluates WHEN predicates in order and returns the first matching THEN; simple CASE compares one expression to successive values, handy for code tables, awkward for ranges.

SQL

Searched CASE: thresholds and ranges

SELECT sample_id, ct,
       CASE
         WHEN ct < 30 THEN 'LOW'
         WHEN ct BETWEEN 30 AND 37 THEN 'OK'
         ELSE 'HIGH'
       END AS band
FROM qpcr_results;

✓Illustrative result set

sample_id	ct	band
S-1	28.1	LOW
S-2	34	OK
S-3	39.4	HIGH

SQL

Simple CASE: map instrument model to platform class

SELECT instrument_id,
       CASE model
         WHEN 'NovaSeq 6000' THEN 'ILLUMINA_HIGH'
         WHEN 'Revio' THEN 'PACBIO_LONG'
         ELSE 'OTHER'
       END AS platform_class
FROM instruments;

✓Illustrative result set

instrument_id	platform_class
SEQ-1	ILLUMINA_HIGH
SEQ-2	PACBIO_LONG

Omitting ELSE

Unmatched rows yield NULL in the CASE column. Downstream GROUP BY buckets them silently into “unknown,” or charts drop them.

Explicit ELSE NULL: or a sentinel: with a comment

Decide whether unknown should be 'UNCLASSIFIED', NULL with documentation, or a hard error via a wrapping CHECK in staging tables.

🧮CASE inside aggregates

Conditional counts and sums without multiple passes.

Portable pattern: SUM(CASE WHEN … THEN 1 ELSE 0 END). PostgreSQL adds COUNT(*) FILTER (WHERE …) for readability, same idea, dialect-specific sugar.

SQL

Count failed vs total wells in one scan

SELECT plate_id,
       COUNT(*) AS wells_total,
       SUM(CASE WHEN call = 'FAIL' THEN 1 ELSE 0 END) AS wells_failed
FROM qc_wells
GROUP BY plate_id;

✓Illustrative result set

plate_id	wells_total	wells_failed
P-01	384	3
P-02	384	0

Determinism

CASE should not depend on wall-clock functions unless you mean it, replays and replicas should see the same labels for the same stored facts.

Growing taxonomies

When WHEN lists sprawl, a join to a dim_qc_band table or enum type often beats a 40-branch CASE in application SQL, BI tools can refresh dimensions without redeploying services.

⚡ Advanced / dialect note

Short-circuit evaluation of CASE is not universally guaranteed for every subexpression in every engine, do not rely on CASE WHEN x <> 0 THEN y/x ELSE NULL END to save you from divide-by-zero unless your dialect documents it; use NULLIF in the denominator instead.

ORDER BY

CASE in ORDER BY is a clean way to sort status enums by business priority (e.g. FAIL before WARN before PASS) without storing redundant sort-key columns, keep the mapping next to the enum definition in one module.

TL;DR

CASE is SQL's conditional expression: CASE WHEN … THEN … [ELSE …] END returns a single scalar per row for use in SELECT, WHERE, GROUP BY, or ORDER BY. Evaluators may optimize, but do not rely on short-circuit semantics for safety across all engines, use NULLIF or guard predicates explicitly. Prefer lookup tables over giant CASE trees when taxonomies change often.