NULLs and COALESCE
Treat missing data deliberately instead of letting it break comparisons.
What you will be able to do
- Test for NULL with IS NULL
- Replace missing values with COALESCE
- Avoid NOT IN surprises
NULL means unknown or not applicable. It is not zero and not an empty string. Any comparison with NULL produces unknown, which filters the row out.
SELECT c.title, l.title AS lesson
FROM courses c
LEFT JOIN lessons l
ON l.course_slug = c.slug AND l.minutes > 15
ORDER BY c.title, l.title;
SELECT c.title,
COALESCE(l.title, 'no long lesson') AS longest
FROM courses c
LEFT JOIN lessons l
ON l.course_slug = c.slug AND l.minutes > 15
ORDER BY c.title;| Expression | Result |
|---|---|
| NULL = NULL | Unknown, not true |
| IS NULL | True when the value is missing |
| COALESCE(a, b) | First non-NULL argument |
| NULLIF(a, b) | NULL when a equals b |
Your turn
MediumTotal minutes with a zero fallback
Return every course title and its total lesson minutes, showing 0 when a course has no lessons.
SQL · starter
SELECT c.title,
SUM(l.minutes) AS minutes
FROM courses c
LEFT JOIN lessons l ON l.course_slug = c.slug
GROUP BY c.id, c.titleCheck your understanding before moving on.
Knowledge checkQuestion 1
Which predicate tests for a missing value?
Select an answer to continue
Study tools
Make it yours.
Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.
Sign in to save notes and flashcards.Both are private to your account and stay with your progress.
Sign inFinished reading?
Lock in what you just learned.
0%
Mark this lesson complete to save your place and move one step closer to the certificate.