The expressions turn the stored columns into answers inside a SELECT. There is the arithmetic for the new metrics, there is the CASE for the conditional labels, and there is whichever branch layout that the row itself dictates. One caveat arrives early, because the integers truncate when they are divided. And one rule lasts. The CASE is the portable conditional, and everything else is a dialect shortcut.
The previous lesson in this module covered "How Does ORDER BY Sort Multi-Level Results?", and the next lesson in this module is about "Which String Functions Format Text?".
What does an arithmetic expression do to the stored numbers?
The expressions compute per row from the values of that row, and then they appear as if they were columns. The archive's efficiency metric, which is the downloads relative to the running minutes, is chainable from nothing but the stored data. Notice the deliberate * 1.0 before the 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.6Why do you multiply by 1.0? Because the SQL engines do the integer division on the integers, and they do it quietly. SQLite started the arithmetic, where 7 divided by 2 is 3 and not 3.5, while the same division against a REAL operand is honest. You promote one side of the slash and the fraction survives.
SELECT 7/2, 7.0/2;7/2 7.0/2
--- -----
3 3.5PostgreSQL makes the integers no safer. The 7/2 truncates there too. MySQL and SQL Server also truncate, with the SQL Server trading away the precision first. The moral is that the type of one operand decides the fate of both. You convert or you cast when a divide intends a fraction, because the silently shortened metrics have ruined more of the monthly reports than any syntax error ever did.
How does the CASE express the row-by-row rules?
The CASE WHEN reads the plainest labelled rows. The search conditions are checked in order, the first match wins, and the ELSE catches the rest. You omit the ELSE and the unmatched rows receive a NULL, which is 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 shortThe order of the branches carries the meaning. The conditions are tested from the top down, and the first match wins, so a range ladder has to start at its far edge. Flip the first two branches above, and every long episode quietly relabels to medium. Nothing errors, and the labels simply lie.
There are two CASE shapes. There is the searched form with the conditions, and there is the simple form that compares one value against a list of candidates. The first one writes the rules, and the second one writes the lookups. Both of them are the standard SQL rather than the per-engine trivia.
What can the CASE do in an ORDER BY?
Because the ORDER BY takes expressions, the CASE supplies the custom sequences. That is the kind that the dashboards and the category rails need.
The featured-tag photography comes first, then the sciences in the id order. It is one clause, and it needs no stored flag.
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 scienceThe trick generalises. The priority ladders with a 0, a 1 and a 2 per category, the weekend before the weekday by the calendar case, and the A, S and Z lanes on the locale, all become sortable. Anything computable becomes sortable.
The index honesty belongs here too. An index cannot power an arbitrary expression sort, so the big result sets will scan and sort in the memory. That is fine at the hundreds and it is costly at the millions.
What about the IF-style shortcut forms?
The same two-way branch has a shorthand in several of the dialects. SQLite has shipped IIF(COND, a, b) since the version 3.32, and the reading rule applies. You use it for the two-way labels that 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 episodeMySQL spells the same idea as IF(). PostgreSQL offers only the CASE, which quietly makes the CASE the portable spelling. The SQL Server grew its own IIF in 2012.
There are two rules of the thumb. You use the CASE when the branches multiply, or when the rule will be reviewed by anybody else. You use the dialect shortcuts only where they keep a one-page query visibly shorter.
Quick reference
- The expressions compute per row and they alias into the virtual columns. They never amend the storage.
- The integer over the integer truncates, so you promote one side, or you cast, whenever the fractions matter.
- The CASE WHEN steps climb in order, so you put the widest branch first or you risk the wrong labels.
- The CASE works anywhere that an expression is legal, and that includes the ORDER BY for the custom orders.
- The IIF and the IF are the two-branch shortcuts, and the CASE is the portable choice across the standard engines.
Transform drill: tag the episodes as 'hit' above 30000 downloads, as 'steady' above 12000, and as 'niche' otherwise, using the CASE. Then rank the downloads of each episode against the season-1 baseline by writing one expression that divides by the constant 9124 with the 1.0 promotion trick. Order the photography episodes first, exactly as it was shown in this lesson. Check two rows by hand, because the CASE mistakes hide in the plain sight and nothing fails loudly.