Null with NOT in sub-query

Question

I've a problem in using NULL with NOT in sub-query, the code is:

Create table #FirstTable(i int)
Create table #secondTable(i int)
insert into #FirstTable(i) values (1),(2),(3),(4),(5)
insert into #secondTable(i) values (2),(3),(null)

select * from #FirstTable where i not in (select i from secondTable);

The expected result would be 1,4,5 but it gives null, how can I get the expected value? The question is somehow similar to the referred question but specifically that question is including joints with different values

score 4 · Accepted Answer · answered Sep 02 '19 at 06:43

4

Using not exists is recommended approach rather than using not in

select * from #FirstTable f
where not exists(select 1 from #secondTable
                 where i = f.i)

answered Sep 02 '19 at 06:43

Michał Turczyn

32,028
14
47
69

There is already an object named '#FirstTable' in the database, SQL server is giving this error. – Zia Ullah Sep 02 '19 at 06:45
1

@ZiaUllah Because you are trying to create this table again with you `create table` statement. You need to remove it. – Michał Turczyn Sep 02 '19 at 06:51
Ahh @michal sorry, I forgot to remove the create table code haha, it worked, thanks a lot ! – Zia Ullah Sep 02 '19 at 06:55

score 1 · Answer 2 · answered Sep 02 '19 at 06:26

its because you have a null value in your 2nd table. filter it using isnull()

Create table #FirstTable(i int)
Create table #secondTable(i int)
insert into #FirstTable(i) values (1),(2),(3),(4),(5)
insert into #secondTable(i) values (2),(3),(null)

select * from #FirstTable where i not in (select isnull(i, 0) from #secondTable);

drop table #secondTable
drop table #FirstTable

score 1 · Answer 3 · answered Sep 02 '19 at 06:28

1

You can add a where i is not null clause to your subquery to avoid this problem:

select * 
from #FirstTable 
where i not in (select i 
                from #secondTable 
                where i is not null);

Demo on dbfiddle

answered Sep 02 '19 at 06:28

Nick

138,499
22
57
95

Null with NOT in sub-query

3 Answers3