CAST and COALESCE fix the two failures that ruin otherwise correct queries. CAST changes what a value is — it turns the text '47 min' into the number 47 so that arithmetic and sorting behave. COALESCE changes what a missing value becomes — it replaces a NULL with a sensible fallback so that a label, a total or a display column never collapses into nothing.
What kind of broken data do CAST and COALESCE repair?
Both functions respond to the same diagnosis: a value arrived in the wrong shape. The runtime_label column holds the running time as human text, which is perfect for a label and useless for a calculation. The guest column holds a name or nothing at all, which is fine for storage and awkward for display. One needs a type conversion, the other needs a default.
How does CAST turn text into a number?
CAST states the target type explicitly. Given a label that begins with digits, the conversion reads the leading number and stops at the first character it cannot use:
SELECT id, runtime_label, CAST(runtime_label AS INTEGER) AS mins
FROM episodes
ORDER BY id;
id runtime_label mins
-- ------------- ----
1 47 min 47
2 38 min 38
3 52 min 52
4 61 min 61
5 44min 44
6 29 min 29
7 35 min 35
8 40 min 40
9 33 min 33
10 27 min 27
Every row produced the right number, including row 5, where the unit is glued to the digits with no space. SQLite reads the numeric prefix and ignores the trailing text. That convenience is a property of this engine's parsing rules rather than a guarantee you can rely on across every database.
What happens when CAST is given something it cannot read?
Two failures, and neither raises an error. Text with no leading digits converts to zero, so a genuinely unparseable value becomes a confident-looking number. And a value that begins with a year converts to that year, because the leading digits are all the parser can see.
SELECT CAST('abc' AS INTEGER) AS text_to_zero,
CAST('12abc' AS INTEGER) AS leading_digits,
CAST('EP-001' AS INTEGER) AS code_to_zero,
CAST('2025-01-08' AS DATE) AS date_cast;
text_to_zero leading_digits code_to_zero date_cast
------------ -------------- ------------ ---------
0 12 0 2025
Read that last column carefully. SQLite has no DATE type in its type system — it recognises INTEGER, REAL, TEXT, BLOB and NUMERIC — so CAST(... AS DATE) falls back to numeric affinity and keeps only the leading number, 2025. A real DATE column in PostgreSQL or SQL Server would have returned the whole date. Prefer extracting the part you want before converting it:
SELECT id, episode_code,
CAST(SUBSTR(episode_code, 4) AS INTEGER) AS seq_num
FROM episodes
ORDER BY id
LIMIT 6;
id episode_code seq_num
-- ------------ -------
1 EP-001 1
2 EP-002 2
3 EP-003 3
4 EP-004 4
5 EP-005 5
6 EP-006 6
Lesson 13 found the dash with INSTR; here the text is sliced to exactly the digits and only then converted. The rule of thumb: parse first, cast second. A cast applied to a value you have not reduced to its meaningful part is a bet that the leading characters happen to be the number you want.
How does COALESCE fill a missing value?
COALESCE takes two or more expressions and returns the first one that is not NULL. Applied to the guest column, it turns the two solo episodes into labelled rows instead of blank ones:
SELECT id, title, COALESCE(guest, 'No guest booked') AS guest_display
FROM episodes
ORDER BY id
LIMIT 6;
id title guest_display
-- ----------------------------------- ---------------
1 Mapping the shore with LiDAR Nadia Okoye
2 The boundary of a map Tom Whitfield
3 Street photography at night Lena Marsh
4 Developing film in a hotel bathroom No guest booked
5 Composing with a single lens Marcus Bell
6 Why the horizon curves Priya Raman
The guest_email column is sparser still — a name can exist without an address on file — so the fallback text appears on four of the first six rows:
SELECT id, guest,
COALESCE(guest_email, 'no email on file') AS contact
FROM episodes
ORDER BY id
LIMIT 6;
id guest contact
-- ------------- --------------------------------
1 Nadia Okoye [email protected]
2 Tom Whitfield no email on file
3 Lena Marsh [email protected]
4 no email on file
5 Marcus Bell [email protected]
6 Priya Raman [email protected]
COALESCE also guards a value you computed. Episode 9 came from a back-catalogue import with no recorded air time, so splitting the timestamp yields NULL — and a NULL in the middle of a report is invisible, which is worse than a wrong value because nobody notices it:
SELECT id, COALESCE(SUBSTR(air_time, 1, 10), 'unknown date') AS air_date
FROM episodes
ORDER BY id;
id air_date
-- -----------
1 2025-03-04
2 2025-03-11
3 2025-01-08
4 2025-01-15
5 2025-02-05
6 2025-03-18
7 2024-11-21
8 2025-05-13
9 unknown date
10 2025-05-27
Row 9 now says something instead of showing a gap. That is the whole purpose of COALESCE: a missing value is a legitimate state, but a report should never leave it to the reader to interpret.
Why does COALESCE accept more than two arguments?
Because a fallback is rarely a single column. The most useful version chains several sources in priority order — email first, then a postal contact, then a literal placeholder — and returns the first one that exists:
SELECT id, COALESCE(guest_email, guest, 'no contact on file') AS contact
FROM episodes
ORDER BY id;
id contact
-- --------------------------------
1 [email protected]
2 Tom Whitfield
3 [email protected]
4 no contact on file
5 [email protected]
6 [email protected]
7 no contact on file
8 Dana Kowalski
9 [email protected]
10 Alina Petrov
Row 2 falls back to the guest name, row 4 falls all the way through to the literal, and row 8 skips the missing email and uses the name too. The evaluation stops at the first non-NULL argument, so the order of the chain is the priority order — putting the literal last guarantees it is reached only when everything else is missing.
When should you use CASE instead of COALESCE?
They overlap in one place and diverge everywhere else. COALESCE replaces a NULL with a value; CASE chooses between branches based on any condition. When the rule is "if the value is missing, use this instead", COALESCE is the shorter and clearer spelling. When the rule is "if it is missing use X, but if it is negative use Y", you need CASE — and lesson 11 built exactly that shape.
There is one more difference worth knowing: COALESCE is a SQL standard function that every major engine implements, while the usual alternatives are not. ISNULL is SQL Server's and SQLite's own spelling, IFNULL is SQLite's and MySQL's, and PostgreSQL offers COALESCE and NULLIF only. When a query has to move between engines, COALESCE is the portable answer.
What is NULLIF for, then?
NULLIF is the third member of this family, and it works in the opposite direction: given two values, it returns NULL when they are equal and the value itself otherwise. That sounds trivial until you use it as a divide-by-zero guard, because it lets a denominator become NULL exactly when it would have been zero.
SELECT 100 / NULLIF(0, 0) AS guarded_divide,
100 / 0 AS raw_divide;
guarded_divide raw_divide
-------------- ----------
NULL NULL
Both cells come back NULL, and that is the point. SQLite does not raise an error on division by zero; it returns NULL for the whole expression, and the failure surfaces far away from its cause — a NULL in a total, a gap in a chart. Wrapping the denominator in NULLIF(denominator, 0) converts a silent NULL into a deliberate one and documents the intent in the query text.
The same guard applies to values that arrive as text. Casting runtime_label gives a number, and sorting by that number rather than by the label is the difference between ranking by minutes and ranking alphabetically by minutes:
SELECT id, runtime_label, CAST(runtime_label AS INTEGER) AS mins
FROM episodes
ORDER BY mins DESC
LIMIT 5;
id runtime_label mins
-- ------------- ----
4 61 min 61
3 52 min 52
1 47 min 47
5 44min 44
8 40 min 40
Sorted as text, '44min' and '40 min' would have compared character by character and produced a different order entirely. The cast changed the column's type for this query only; the stored text is untouched — exactly the boundary between a read query and the UPDATE statements of module 07.
Quick reference
- CAST changes the type of a value; it never changes the stored row.
- Parse first, cast second — slice to the meaningful characters before converting.
- A cast that cannot read its input returns zero rather than an error, so validate before you convert.
- COALESCE returns the first non-NULL argument, which makes argument order the priority order.
- Use COALESCE for "missing means default", CASE for "the rule has branches", NULLIF to guard a denominator.
Repair drill: the archive wants a printable label for every episode in the form 'EP-004 — 61 min'. Build it from episode_code and runtime_label alone, add a fallback for the missing guest_email, and then write a second query that refuses to trust any label that does not begin with a digit — deciding for yourself whether CASE or a WHERE clause is the right place for that check.