IN clause with NULL values in SQL is broken!

Опубликовано: 04 Октябрь 2024
на канале: ETL-SQL

637

Free SQL Pattern Training: https://etlsql.kartra.com/page/sps-fr...
Do you understand SQL but still cannot write SQL queries correctly ?
Are you afraid to switch jobs because of the SQL interview rounds or have you failed SQL Interview earlier ?
If you are someone who understands SQL and need more confidence to solve SQL queries correctly, I would like to share my recent online course especially created for you.
Check for more details: https://etlsql.kartra.com/page/sql-pa...

In this video, we will see if IN clause can handle NULL values coming from subquery.
Video Transcript:
00:00 Do you think that IN clause can handle null values in SQL? So let's do a quick test to confirm it.
00:09
I've created a table product with some dummy rows in it and there are two rows which has null value for the price column.
00:17
So in this test what I will do is I will run a subquery.
00:25
On this table to see.
00:27
If the IN clause handles the null values and returns the output, what do you think will be the output of this query?
00:35
So we can see that in the output the two rows are missing the row #3 and row number six where the price value was null.
00:43
So both the rows are missing.
00:45
It means that IN does not handle the null values.
00:49
So if there are null values coming, you will not see it in the output if you are using IN clause
00:54
I'm pretty sure if I run only this part select distinct price from products, I will see the NULL values also in the output.
01:03
So it means the list actually has null value.
01:06
however when we are using it as a subquery using an IN clause, it does not resulting the result with the value NULL.
01:15
So there are two ways to handle it.
01:17
One is obviously you can add a function like coalesce to give a default value to all the null values.
01:26
So I will add coalesce on both the sides and give minus one on both the sides so that it matches.
01:33
And when I run this query I can see that both the records where the value was null is now coming in the output.
01:39
If you don't want to use this approach, then another approach can be that you explicitly add a condition or price is null
01:49
null always comes with IS.
01:50
You cannot use any other operator rather than IS.
01:53
So if I run this query now, so you can see all the rows are in the output right? So these two ways you can use to handle null values in your SQL queries.
02:04
In does not support it.
02:06
Now my question to you is if instead of IN if I use NOT IN, do you think I'll see the rows in the output?