Expressions turn stored columns into answers inside a SELECT: arithmetic for new metrics, CASE for conditional labels, and whichever branch layout the row itself dictates. One caveat arrives early — integers truncate when divided — and one rule lasts: CASE is the portable conditional, everything else is a dialect shortcut.
What does an arithmetic expression do to stored numbers?
Expressions compute per row from a row's own values, then appear as if they were columns. The archive's efficiency metric — downloads relative to running minutes — is chainable from nothing but stored data. Note the deliberate * 1.0 before division:
SELECT title, duration,
ROUND(downloads * 1.0 / duration, 1) AS per_minute
FROM episodes
ORDER BY per_minute DESC LIMIT 5;
title duration per_minute
----------------------------------- -------- ----------
Kitchen chemistry at altitude 40.0 892.8
Developing film in a hotel bathroom 61.0 675.5
The photograph nobody printed 33.0 660.6
Street photography at night 52.0 642.6
Why the horizon curves 29.0 616.6
Why multiply by 1.0? Because SQL engines do integer division on integers, and quietly: SQLite started the arithmetic — 7 divided by 2 is 3, not 3.5 — while the same division against a REAL operand is honest. Promote one side of the slash and the fraction survives:
SELECT 7/2, 7.0/2;
7/2 7.0/2
--- -----
3 3.5
PostgreSQL makes integers no safer — 7/2 truncates there too; MySQL and SQL Server also truncate, with SQL Server trading away precision first. The moral: the type of one operand decides the fate of both. Convert or cast when a divide intends a fraction; silently shortened metrics have ruined more monthly reports than any syntax error.
How does CASE express row-by-row rules?
CASE WHEN reads plainest labelled rows. Search conditions checked in order, first match wins, ELSE catches the rest (omit ELSE and unmatched rows receive NULL — rarely what you want on a label):
SELECT title, duration,
CASE WHEN duration >= 50 THEN 'long'
WHEN duration >= 35 THEN 'medium'
ELSE 'short'
END AS size
FROM episodes ORDER BY id;
title duration size
----------------------------------- -------- ------
Mapping the shore with LiDAR 47.0 medium
The boundary of a map 38.0 medium
Street photography at night 52.0 long
Developing film in a hotel bathroom 61.0 long
Composing with a single lens 44.0 medium
Why the horizon curves 29.0 short
Reading an old survey 35.0 medium
Kitchen chemistry at altitude 40.0 medium
The photograph nobody printed 33.0 short
Naming wind and weather 27.0 short
Branch order carries meaning: conditions are tested top-down and the first match wins, so a range ladder must start at its far edge. Flip the first two branches above and every long episode quietly relabels to medium — nothing errors, the labels simply lie. Two CASE shapes exist: this searched form with conditions, and the simple form comparing one value against a list of candidates — the first writes rules, the second writes lookups, and both are standard SQL rather than per-engine trivia.
What can CASE do in an ORDER BY?
Because ORDER BY takes expressions, CASE supplies custom sequences — the kind ML dashboards and category rails need. Featured-tag photography first, then sciences in id order, one clause, no stored flag required:
SELECT title, genre FROM episodes
ORDER BY CASE WHEN genre = 'photography' THEN 0 ELSE 1 END, id;
title genre
----------------------------------- -----------
Street photography at night photography
Developing film in a hotel bathroom photography
Composing with a single lens photography
The photograph nobody printed photography
Mapping the shore with LiDAR science
The boundary of a map science
Why the horizon curves science
Reading an old survey science
Kitchen chemistry at altitude science
Naming wind and weather science
The trick generalises: priority ladders (0, 1, 2 per category), weekend-before-weekday by calendar case, or A/S/Z lanes on locale — anything computable becomes sortable. Index honesty belongs here too: an index cannot power an arbitrary expression sort, so big result sets will scan and sort in memory. Fine at hundreds — costly at millions.
What about the IF-style shortcut forms?
The same two-way branch has a shorthand in several dialects — SQLite has shipped IIF(COND, a, b) since 3.32 — and the reading rule applies: use it for two-way labels you understand at a glance:
SELECT id, title, IIF(guest IS NULL, 'solo episode', 'guest episode') AS kind
FROM episodes ORDER BY id LIMIT 6;
id title kind
-- ----------------------------------- -------------
1 Mapping the shore with LiDAR guest episode
2 The boundary of a map guest episode
3 Street photography at night guest episode
4 Developing film in a hotel bathroom solo episode
5 Composing with a single lens guest episode
6 Why the horizon curves guest episode
MySQL spells the same idea IF(); PostgreSQL offers only CASE, which quietly makes CASE the portable spelling; SQL Server grew its own IIF in 2012. Rules of thumb: use CASE when the branches multiply or the rule will be reviewed by anyone else, dialect shortcuts only where they keep a one-page query visibly shorter.
Quick reference
- Expressions compute per row and alias into virtual columns; they never amends storage.
- Integer over integer truncates — promote one side (or CAST) whenever fractions matter.
- CASE WHEN steps climb in order; put the widest branch first or risk wrong labels.
- CASE works anywhere an expression is legal — including ORDER BY for custom orders.
- IIF / IF are two-branch shortcuts; CASE is the portable choice across the standard engines.
Transform drill: tag episodes 'hit' above 30000 downloads, 'steady' above 12000, else 'niche', using CASE, then rank each episode's downloads against the season-1 baseline by writing one expression that divides by the constant 9124 with the 1.0 promotion trick, and order photography episodes first exactly as shown in this lesson. Sanity check two rows by hand — CASE mistakes hide in plain sight because nothing fails loudly.