0:00You ask for every customer whose city isn't Paris. You get a list back.. and one of your customers isn't on it.
0:07Nothing failed, and nothing warned you. That customer's city is missing.. and a comparison against a missing value isn't true. It isn't false either.
0:17So we'll start with what sequel actually returns when you compare against something that isn't there.
0:23Then the right way to ask whether a value is missing, and how to show a stand-in without touching what's stored.
0:30Then two questions that look identical and aren't.. how many roes, versus how many values.
0:36And we'll finish with the joins that quietly throw away the roes you were trying to keep.
0:42Start with the simplest question you can ask about missing data. Are two missing values the same?
0:49Both sides here are unknown. Not blank, not zero.. we just don't know what's there.
0:55And the answer comes back unknown too. If you don't know either value, you can't know whether they match.
1:01So sequel doesn't have two logical results. It has three.
1:06Unknown is a real outcome, not an error. Every comparison that touches a missing value lands right here.
1:13And that's what makes it easy to miss. A filter only lets through what's true.. so false and unknown get exactly the same treatment.
1:22Here's the table we'll use for the rest of this. Four customers, and one of them has no city on file.
1:27Ben's city is missing. Not an empty string, not a space, not the word null typed into the column. There's no value there at all.
1:36So let's go find him. The obvious move is to ask which customers have a city equal to nothing.
1:44And we get nobody. Ben is sitting right there in the table, and this filter cannot see him.
1:50Because comparing to a missing value never comes back true. To ask about missing data you need an operator that tests for absence.
1:59This one does exactly that, instead of comparing. And there's Ben. Negate it, and you get the other three.
2:06Finding him is one problem. Showing him is another. Coalesce reads its arguments left to right and hands back the first one that isn't missing.
2:15So Ben gets something readable. And his stored city is still empty.. we changed what came out, not what's in the table.
2:23Now a counting question. This one asks two things at the same time.
2:27The first counts roes. It never looks inside them, so nothing in the data can change the answer.
2:32The second counts values in a column. That turns out to be a different question entirely.
2:38Four customers.
2:39But three cities. Ben's roe still gets counted.. his missing city doesn't.
2:44So choose deliberately. One of these tells you how many customers you have. The other tells you how much you actually know about them.
2:53Here's a filter that looks completely safe.
2:56Every customer whose city isn't Paris. Chloe lives in Paris, so she drops out. That should leave us three.
3:02Two. Ben is gone as well, and nothing in that query asked for him to go.
3:07His city is unknown. So is it Paris? Nobody can say.
3:11And a filter that can't get to true won't return the roe. Not equal to Paris quietly means known, and not Paris.
3:18If you want the unknowns as well, you have to ask for them out loud.
3:22And Ben's back. Sequel won't guess what you meant.. so anything you want included has to be in the query.
3:29Second table. Four orders.. and one of them didn't come from a customer we know about.
3:34Somebody checked out as a guest. No account, so there's no customer id to record.
3:40Now, Ben has never ordered anything. His id is two, and two isn't among the IDs that placed orders.
3:48So this should hand him straight back. Customers whose ID is not in the set of ids that ordered.
3:54Nothing at all. Not Ben, not anybody. And no error to tell you something went wrong.
3:59Here's what that condition really becomes for Ben. Three comparisons he passes.. and one nobody can settle.
4:07Is two different from a value we don't have? There's no answer. And one unanswerable term is enough to stop the whole chain ever reaching true.
4:17So ask a different question. Not whether his ID is absent from a list.. but whether a matching order exists at all.
4:24That one is answerable. No order matches Ben, so Ben comes back.
4:29Here's a case where missing values cost you the most.
4:32We want every customer listed, with their shipped order if they have one. So we do a LEFT Join. And we expect all four customers.
4:41But we get, two of them. The left join was supposed to keep all four, and half of them vanished on the way out.
4:48Chloe did match an order. It was cancelled, so the status check on her roe comes back false.
4:54Ben matched nothing, so the join had no status to give him. His check can't come back true or false.
5:01Two different reasons, and the filter treats them the same. The real problem is where that condition sits.
5:09Move it up into the join itself. Now it decides which orders are allowed to match.. instead of deciding which customers survive.
5:18All four are back. Ben has no order, so his status is empty.
5:22Chloe has an order, but it isn't a shipped one, so it doesn't match and she's empty too.
5:28There's a second fix you'll see, and it answers a different question. This one keeps customers who have no orders whatsoever.
5:36Which brings Ben back, but not Chloe.. because she does have an order. Same join, different requirement. Pick the one you actually meant.
5:46One order in that table never got a price. Let's add five to it and see what ordinary arithmetic does.
5:54Still nothing. Add five to a number you don't have, and you still don't have a number. Missing spreads through the whole expression.
6:03Aggregates behave completely differently. This one asks for the total, the roe count, the count of known prices, and the average.
6:13Four orders in the table, but only three of them have a price. That three is the number to watch.
6:19Because it's the denominator. Sum, average, min and max all step over the missing values rather than choking on them.
6:26A hundred across three known prices. Not four.
6:29Grouping has its own answer. We already know that two unknowns aren't equal to each other.. so what happens when you group by a column that has one?
6:39They get a group. Ben's roe is counted, under a key that has no value.
6:43And a second customer with no city would land in that same bucket, not start a new one. Grouping gathers the missing together instead of comparing them.
6:53So that's the whole picture.
6:55A missing value isn't a wrong value, and knowing the difference is most of the battle.
7:01You've got a way to ask whether something is there, and a way to put a stand-in over the gap.
7:06You know which counts ignore the gaps, and that grouping gathers them instead.
7:10And you know the two places roes go missing.. a list with an unknown in it, and a condition sitting after the join instead of inside it.
7:19Here's one to try. Every customer, with a count of their shipped orders. Both traps from this video are waiting in it.. and a customer with none should come out as zero.

SQL NULL Semantics

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 7 min 38 sec

0 views

About this video

NULL means unknown, not empty, and most of the silent wrong answers in SQL interviews come from forgetting that. Two small tables, one with a missing city and one with a guest checkout, show every case.

You'll review how to:

Read three-valued logic and why WHERE keeps only rows that test TRUE
Test for NULL with IS NULL and IS NOT NULL, and supply defaults with COALESCE
See which rows an inequality filter, an aggregate or a GROUP BY quietly drops or keeps
Avoid the NOT IN trap when the list can contain NULL, and place a LEFT JOIN filter in ON rather than WHERE

For engineers who know SELECT, WHERE, GROUP BY and basic joins. The classic silent-wrong-answer trap, worked end to end.

What changed

v103 Oct 2026abhydesai7 min 38 sec037acd5

First published version.

0 views0 likes on this version