1

Here is my Table1

personid
1
?
2
3
4
?
6

Here is my query

select * 
from table2
where personid not in 
(
select personid
from table1
)

The result is nothing

Here is my second query

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

The result is ok

Question : why the first query did not work ? I can't see any logical problem . Do nulls skrew up teradata ?

Rob Paller
  • 7,616
  • 25
  • 24
Buras
  • 2,899
  • 27
  • 78
  • 122

2 Answers2

5

It's not specific to Teradata, it's the same (or at least should be the same) in all RDBMSes. Any comparison to a NULL results in UNKNOWN and NOT IN is an ANDed condition:

personid <> 1st_value_in_list AND personid <> 2nd_value_in_list AND ... AND personid <> NULL

This has been discussed multiple times, e.g. NOT IN clause and NULL values

Community
  • 1
  • 1
dnoeth
  • 57,618
  • 3
  • 33
  • 50
1

Yes. If one of the value in the subquery is NULL, the IN clause does not return anything.

Lenin Raj Rajasekaran
  • 21,611
  • 14
  • 96
  • 136