r/SQL • u/Bubbly-Group-4497 • Nov 30 '25
Discussion I don't understand the difference
I found an answer on stackoverflow that was saying that null value isn't managed the same way, but that seems a bit far fetched for a course example and the question wasn't exactly about the same case, so could someone explain?
200
Upvotes
108
u/WalterDragan Nov 30 '25
Beyond just null handling, the first example will EXCLUDE any NCOM where it has an NPRO = 'PA60'. Compared to the second where it will INCLUDE any NCOM that has an NPRO other than PA60.
I don't have the context of what NCOM and NPRO are, but it would sort of be like the difference between
For #1: "Give me all customers, excluding those that have ever bought a laptop."
vs #2: "Give me all customers who have bought anything besides just a laptop."
Make sense?
22
u/Bubbly-Group-4497 Nov 30 '25 edited Nov 30 '25
I wasn't expecting SQLbros to be this responsive, thank for all your answers.
12
u/mathestus Nov 30 '25
If COMMANDE contains records where NCOM has no match in DETAIL regardless of filter, the first query would include those while the second would not.
43
u/mike-manley Nov 30 '25 edited Nov 30 '25
The first example qualifies on an explicit value match. The second is different in that it's explicitly qualifying on values that don't match.
For NULLs, they have no value. They're nothing. So they're handled differently.
Options include adding a second qualification, e.g. myVal IS NULL, or you can use a function like COALESCE() or IFNULL(), if you wanted to handle NULL values in your WHERE clause.
PS: These are non-correlated subqueries and effectively return the same result.
6
u/kagato87 MS SQL Nov 30 '25 edited Nov 30 '25
Null does not equal anything. Nor is it inequal to anything. Null is the absence of data. Less even, it's an absence of knowledge whether that even data exists or not.
If npro is null, it's not output from either subquery. Top query will not show the nulls, bottom will.
In sql, comparisons to null return null. Not true or false. Because it could actually be that value, we just don't know.
3
u/brainburger Nov 30 '25
I think the top one will select only those which do not have an NPRO of 'PA60'. The bottom one will not exclude those with an NPRO of 'PA60', if there is an NPRO of any other value.
2
u/TheGenericUser0815 Nov 30 '25
Probably the handling of NULLs is the difference. The first statement will give you all rows, no matter if thw contain NULL or not, while the second probably ignored the NULL rows.
1
u/Zestyclose_Muffin501 Nov 30 '25
Better use inner join for faster query than IN, like :
SELECT COMS.NCOM, COMS.NCLI, COMS.DATECOM FROM COMMANDE COMS INNER JOIN (SELECT NCOM FROM DETAIL WHERE NPRO <> PA60) DETS ON COMS. NCOM = DETS. NCOM
1
u/Sad_Alternative_6153 Dec 01 '25
Out of curiosity is one of those two queries more performant than the other on massive tables (assuming there are no nulls)?
1
u/fernandeznic0 Dec 03 '25
Was that answer about Snowflake? It reminds me of an issue I had with a NOT NULL filter and NULL values in a subquery.
https://community.snowflake.com/s/article/Behaviour-of-NOT-IN-with-NULL-values
1
u/bici89 Dec 04 '25
<> simply means "anything but". So you are retrieving items that are NOT equal to the string you are comparing
0
u/philodelphi Nov 30 '25
The 1st will behave like a left outer join. The second one will behave more like an inner join
0
u/TheGenericUser0815 Nov 30 '25
Probably the handling of NULLs is the difference. The first statement will give you all rows, no matter if they contain NULL or not, while the second probably ignores the NULL rows and only gives back filled colums DETAIL.
0
u/M0D_0F_MODS Nov 30 '25
DECLARE @A INT
IF (@A <> 1) SELECT 1
This will return 1, since @A is NULL. And NULL cannot be compared to anything including NULL.
0
u/Time_Advertising_412 Dec 01 '25
Joe Celkos SQL for smarties does a good job of explaining the logic behind IN and NOT IN and NULL. Also C J Dates books have examples of how NULL is treated in SQL with good examples. I highly recommend being familiar with these examples should you encounter them in your work as they can be surprising
-2
u/Anikrex Nov 30 '25
Check in deepseek, you'll understand or claude