CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Anyname Donotcare
Anyname Donotcare

Reputation: 11403

Why doesn't the [NOT IN ] syntax work?

I try to execute the following query but it doesn't get any data although it should get one row :

select * from [DB1.table1] where col1 not in (select col2 from DB2.table2)

col1,col2 are of type varchar

why it doesn't work ?

Upvotes: 1

Views: 72

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

As others have already pointed to the reason that cause this issue, you can achieve the same results, using LEFT JOIN and it safe than the predicate IN with the NULL vlaues:

select t1.* 
from [DB1.table1] AS T1 
LEFT JOIN  DB2.table2 AS t2 ON t1.col1 = t2.col2
where t1.col2 IS NULL;

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239734

If you have NULLs in col2 in table2, you'll get the behaviour you describe:

create table table2 (
    col2 varchar(10) null
)
insert into table2 (col2) values ('abc'),(null)
create table table1 (
    col1 varchar(10) null
)
insert into table1 (col1) values ('abc'),('def')

select * from table1 where col1 not in (select col2 from table2)

Produces no rows. This is because the result of NOT IN becomes UNKNOWN once a NULL comparison occurs.

You can fix it with:

select * from table1 where col1 not in (select col2 from table2 where col2 is not null)

If that's the correct logic for your situtation.

Upvotes: 1

user330315
user330315

Reputation:

"Doesn't work" isn't exactly a good description of your problem, but in nearly all cases this is caused by the sub-select returning NULL values.

You probably want this:

select * from [DB1.table1] 
where col1 not in (select col2 from DB2.table2 where col2 is not null);

The comparison with NULL always yield "undefined" and thus if at least one row from the sub-select contains a NULL in the col2 column the whole expression is "undefined". As undefined not "true", the whole query doesn't return anything.

Upvotes: 5

Related Questions