The CAST and the COALESCE fix the two failures that ruin the otherwise correct queries. The CAST changes what a value is, because it turns the text '47 min' into the number 47 so that the arithmetic and the sorting behave. The COALESCE changes what a missing value becomes, because it replaces a NULL with a sensible fallback so that a label, a total or a display column never collapses into nothing.
The previous lesson in this module covered "What Do Number and Date Functions Compute?", and this lesson is the last one in the Sorting, Functions and Shaping module.
What kind of broken data do the CAST and the COALESCE repair?
Both of the functions respond to the same diagnosis. A value arrived in the wrong shape.
The column runtime_label holds the running time as the human text, which is perfect for a label and useless for a calculation. The column guest holds a name or nothing at all, which is fine for the storage and awkward for the display. One of them needs a type conversion, and the other one needs a default.
How does the CAST turn the text into a number?
The CAST states the target type explicitly. Given a label that begins with the digits, the conversion reads the leading number and it stops at the first character that 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 27Every row produced the right number, including the row 5, where the unit is glued to the digits with no space. SQLite reads the numeric prefix and it ignores the trailing text. That convenience is a property of the parsing rules of this engine, and not a guarantee that you can rely on across every database.
What happens when the CAST is given something that it cannot read?
There are two failures, and neither one raises an error. The text with no leading digits converts to the 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 that 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 2025Read that last column carefully. SQLite has no DATE type in its type system. It recognises the INTEGER, the REAL, the TEXT, the BLOB and the NUMERIC. So the CAST(... AS DATE) falls back to the numeric affinity and it keeps only the leading number, which is 2025. A real DATE column in PostgreSQL or in the SQL Server would have returned the whole date.
You prefer to extract the part that you want before you convert 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 6Lesson 13 found the dash with the INSTR, and here the text is sliced to exactly the digits and only then it is converted. The rule of the thumb is this one. Parse first and cast second. A cast that is applied to a value that you have not reduced to its meaningful part is a bet that the leading characters happen to be the number that you want.
How does the COALESCE fill a missing value?
The COALESCE takes two or more expressions and it returns the first one that is not a NULL. Applied to the column guest, it turns the two solo episodes into labelled rows instead of the 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 RamanThe column guest_email is sparser still, because a name can exist without an address on the file. 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]The COALESCE also guards a value that you computed. The episode 9 came from a back-catalogue import with no recorded air time, so the splitting of the timestamp yields a 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-27The row 9 now says something instead of showing a gap. That is the whole purpose of the COALESCE. A missing value is a legitimate state, but a report should never leave it to the reader to interpret.
Why does the COALESCE accept more than two arguments?
Because a fallback is rarely a single column. The most useful version chains several sources in the priority order, which is the email first, then the postal contact, and then a literal placeholder. It 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 PetrovThe row 2 falls back to the guest name, the row 4 falls all the way through to the literal, and the 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 that it is reached only when everything else is missing.
When should you use the CASE instead of the COALESCE?
They overlap in one place and they diverge everywhere else. The COALESCE replaces a NULL with a value, while the CASE chooses between the branches based on any condition.
When the rule is "if the value is missing, use this instead", the COALESCE is the shorter and the clearer spelling. When the rule is "if it is missing use the X, but if it is negative use the Y", then you need the CASE, and lesson 11 built exactly that shape.
There is one more difference that is worth knowing. The COALESCE is a SQL standard function that every major engine implements, while the usual alternatives are not. The ISNULL is the spelling of the SQL Server and of SQLite itself, the IFNULL is the spelling of SQLite and of MySQL, and PostgreSQL offers the COALESCE and the NULLIF only. When a query has to move between the engines, the COALESCE is the portable answer.
What is the NULLIF for, then?
The NULLIF is the third member of this family, and it works in the opposite direction. Given two values, it returns a 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 a NULL exactly when it would have been the zero.
SELECT 100 / NULLIF(0, 0) AS guarded_divide,
100 / 0 AS raw_divide;guarded_divide raw_divide
-------------- ----------
NULL NULLBoth of the cells come back a NULL, and that is the point. SQLite does not raise an error on the division by zero. It returns a NULL for the whole expression, and the failure surfaces far away from its cause, as a NULL in a total or a gap in a chart. Wrapping the denominator in the NULLIF(denominator, 0) converts a silent NULL into a deliberate one, and it documents the intent in the text of the query.
The same guard applies to the values that arrive as the text. The casting of the runtime_label gives you a number, and the sorting by that number rather than by the label is the difference between the ranking by the minutes and the ranking alphabetically by the 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 40Sorted as the text, the '44min' and the '40 min' would have compared the character by character and they would have produced a different order entirely. The cast changed the type of the column for this query only. The stored text is untouched, which is exactly the boundary between a read query and the UPDATE statements of module 07.
Quick reference
- The CAST changes the type of a value, and it never changes the stored row.
- Parse first and cast second. You slice to the meaningful characters before you convert.
- A cast that cannot read its input returns the zero rather than an error, so you validate before you convert.
- The COALESCE returns the first non-NULL argument, which makes the argument order the priority order.
- You use the COALESCE for "missing means default", the CASE for "the rule has branches", and the NULLIF to guard a denominator.
Repair drill: the archive wants a printable label for every episode in the form 'EP-004 — 61 min'. You build it from the episode_code and the runtime_label alone. You add a fallback for the missing guest_email. And then you write a second query that refuses to trust any label that does not begin with a digit, and you decide for yourself whether the CASE or the WHERE clause is the right place for that check.