0

Can any one explain to me why I'm getting different results:

Table2.Table1ID nullable field

SELECT * FROM Table1
WHERE
ID NOT IN (SELECT Table1ID FROM Table2)

no results

SELECT * FROM Table1
WHERE
ID NOT IN (SELECT Table1ID FROM Table2 WHERE Table1ID IS NOT NULL)

expected results: Table1 items which are not related to Table2

SQL server version: 10.50.3720.0 OS: Microsoft Windows NT 6.1 (7601)

cinek
  • 1,888
  • 2
  • 13
  • 14

2 Answers2

1

If any of the values in the Table2 has null then First query gets transalted to

SELECT * FROM Table1
WHERE (ID <> NULL and ID <> SomeValue and...)

ID<>NULL is unkown .Hence you don't get any result

where as, in your 2nd query you are eliminating the NULL .Hence you don't get an unknown value .

You can also write your query using Not Exists

SELECT * FROM Table1
WHERE
NOT Exists (SELECT 1  FROM Table2 where Table1ID = ID )
praveen
  • 12,083
  • 1
  • 41
  • 49
  • I think those should be `and`s between alternatives in your translation. Otherwise, if there's more than one distinct value, then obviously one of `ID<>Value1` or `ID<>Value2` will always be true. – Damien_The_Unbeliever Feb 07 '14 at 11:56
  • @Damien_The_Unbeliever: Yeah my mistake . `OR ` holds true for `In` operation – praveen Feb 07 '14 at 11:57
  • I've fixed your query, but 'NOT EXISTS' gives correct results. – cinek Feb 07 '14 at 12:18
  • Correct should be: 'SELECT * FROM Table1 WHERE NOT Exists (SELECT 1 FROM Table2 where Table1.ID = ID )' – cinek Feb 12 '14 at 14:38
0
SELECT * FROM Table1
WHERE
ID NOT IN (SELECT ISNULL(Table1ID, ' ') FROM Table2)

NOT IN with NULL will end up in failing all condition! It is same with oracle as well!

NOT IN... NULL obviousy is hypothetical!.. Not in nothing, literally even is a not a valid one isn't it!

NULL is something, like not defined.. So we have special functions available to handle NULL. Even for comparison we use IS NULL and Not = NULL

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69