NULLs & COALESCE (Learn SQL)

An empty cell is not a value, so comparing it to one answers neither yes nor no. Why an equals sign against NULL matches nothing at all, what SQL's third truth value does to your WHERE, and the two places a missing value quietly changes a number you were about to send someone. ▶ HANDS-ON, NOT A LECTURE: you type every query yourself and run it on the Northwind sample database. Then you practise with runnable koans — a query with a blank to fill in, one for every idea in this video, that goes green when you get it right. Practice is king. ⚙ FIRST TIME? SET UP HERE (a few minutes): https://datapallas.com/learn-data Northwind PostgreSQL and CloudBeaver are both bundled with DataPallas — nothing extra to install. 🔗 THIS LESSON, WRITTEN OUT: https://datapallas.com/data-academy/learn-... CHAPTERS 00:00 What this course is, and what Series 1 covers 00:30 Open the koans first — practise while you watch 01:19 The empty cells you made, and the question about them 01:52 The obvious query for the unshipped orders 02:06 Zero rows, 27 unshipped, and no error 02:32 NULL is not a value, it is the absence of one 03:19 True, false and unknown — SQL's third answer 03:44 IS NULL, the test that always answers 04:13 An empty string is not a NULL, and = '' finds neither 05:16 Two reports that are supposed to cover the year 05:36 How many orders shipped in 2024 or later 05:57 How many shipped before 2024, and why 18 plus 34 is not 79 06:22 The same 27 rows, missing from both reports 06:52 Wrapping the condition in NOT changes nothing 07:17 Why NOT does not flip unknown 07:50 The two cells of the AND and OR tables that matter 08:11 Saying out loud what happens to the empty rows 08:32 The rule: can this column be empty? 09:00 GROUP BY the shipping year — predict how many piles 09:19 Four piles, not three — the empties get one of their own 09:47 A join ON a column with blanks matches nothing 10:22 NULLs spread into your output too 10:44 One empty piece empties the whole line 11:00 COALESCE — put something there 11:28 NULLIF — take something away on purpose 11:52 Count star against count of a column, and now the reason 12:30 How long do we take to ship an order? 12:46 The average that leaves out every late order 13:25 Fixing the dashboard tile that comes back empty 14:04 Sorting keeps them — but DuckDB and PostgreSQL disagree where 14:45 Four places to look, and one question to ask 15:14 What's next in Series 1 — your first real report THE ONE IDEA A NULL is the absence of a value, so a comparison against it is neither true nor false — it is UNKNOWN. A WHERE keeps only the rows that came back TRUE, discarding unknowns in the same silence as falses. Every bug in this video is that one sentence, met in a different clause. WHAT YOU CAN WRITE BY THE END • IS NULL and IS NOT NULL — the only tests for a missing value that answer • Filters that say out loud what happens to the empty rows • COALESCE — stand a value in for a missing one, in the right place • NULLIF — make a value missing on purpose, and guard a division with it • Counts and averages you can defend, because you checked the denominator WHY IT MATTERS: asked how many orders have not shipped, the obvious query returns ZERO while 27 sit unshipped, the oldest since December 2022. It does not fail or warn. Read as good news it says "we are all caught up", with two years of backlog underneath. ALSO COVERS • Two correct queries — orders shipped on or after 1 January 2024, and orders shipped before it — return 18 and 34 out of 79. Both right; together they lose 27 rows and never mention it • Why NOT changes nothing: it flips true and false and leaves unknown where it was • Why an empty string is not a blank: = '' finds none of the empties, and NOT an empty string drops them too • Why a join loses them too — an ON is a comparison, so joining on a nullable column drops every such row, with no error • The one clause that loses nothing: a sort keeps all 79 rows, but DuckDB and PostgreSQL disagree where blanks land unless you write NULLS LAST • The one place NULLs ARE equal: DISTINCT and GROUP BY gather them into one group • Thirteen koans, from one blank to a whole query written from scratch "It ran, and it lied. If I'd sent that upstairs I'd have said good news, we're all caught up," says Leo, looking at an empty result and two years of backlog. Taught by Mnemosyne (the teacher) and Leo (the learner) — because writing SQL will soon feel as natural as speaking English. Three-valued logic — true, false and unknown — comes from Codd's relational model. 📚 The whole course: https://datapallas.com/data-academy/learn-sql ⭐ DataZeus — the open-source home of every course here. A star helps the next person find it: https://github.com/flowkraft/datazeus #sql #sqltutorial #learnsql