Conditional Logic In SQL
The CASE expression is SQL conditional logic. It evaluates conditions in order and returns the value of the first one that is true, much like an if else chain. It can appear in SELECT, WHERE, ORDER BY, and aggregates.
Two Forms
CASE comes in two shapes:
- Searched CASE lists full conditions, like WHEN score greater than ninety THEN A.
- Simple CASE compares one expression to values, like CASE status WHEN one THEN active.
- An optional ELSE supplies a default, otherwise unmatched rows return null.
Common Uses
CASE powers many everyday tasks:
- Bucketing values into labels like low, medium, and high.
- Conditional aggregation, summing only rows that match a condition.
- Custom sort orders inside ORDER BY.
A Subtle Point
Conditions are tested top to bottom, so order matters when ranges overlap. Also, every branch should return a compatible type, or the engine may raise a type error.
Key idea
CASE evaluates conditions in order and returns the first matching value, powering buckets, conditional aggregates, and custom sort orders inside SQL.