0

I am stuck with a query that does not return any value despite it should. I have

Table1 with around 50000 different IDs, and Table2 with around 1000 different IDs. I run this query:

Select * From Table1 T1
Where T1.ID not in ( Select Id From Table2 T2 Where 1 = 1 )

I did expect this query to give me at least 49000 rows but I do not get a single one. Now, when I run 

Select * From Table1 T1
Where T1.ID not in ( Select Id From Table2 T2 Where T2.Id = T1.Id )

I do get my expected result (which is 49000 rows). However I does not feel very logic to me. From a set theoretical point of view the second where clause does not make sense and the first query just should work anyways...

Ori Marko
  • 56,308
  • 23
  • 131
  • 233
Peter Frey
  • 361
  • 4
  • 17
  • 3
    Your Subquery probably returns a NULL (search for *three valued logic*), try adding a `WHERE id IS NOT NULL` (or switch to `Where NOT EXISTS ( Select * From Table2 T2 Where T2.Id = T1.Id )` – dnoeth Jul 17 '19 at 15:54
  • Ah right :-) thanks - I actually got a null in T2. – Peter Frey Jul 17 '19 at 15:59
  • @dnoeth: PS: Thanks for the "3VL" reference. After reading this I feel that SQL fails in applying the result that "5 in (1,2,3,4,5,null) is false" because I understand an IN clause as an iterated OR clause. True OR unknown is true in 3VL. However I can live with that. SQL can have it's own rules... – Peter Frey Jul 25 '19 at 08:42
  • "5 in (1,2,3,4,5,null) is false" is wrong, for ORed conditions any comparison returning in TRUE results in TRUE, thus the NULL is simply ignored. But "5 NOT in (1,2,3,4,5,null)" are ANDed conditions and any UNKNOWN comparison to NULL results in UNKNOWN, which fails to return an answer set. – dnoeth Jul 25 '19 at 11:59
  • Hi Dnoeth, thanks for clarification! I thought I checked both cases but appearently you are right! – Peter Frey Jul 29 '19 at 07:51

1 Answers1

0

As dnoeth pointed out:

If I have NULL in my selection the IN-clause will not work. So at the end I will use:

Select * From Table1 T1
Where T1.ID not in ( Select Id From Table2 T2 Where 1 = 1 and T2.Id is not null)

Thanks to dnoeth.

Peter Frey
  • 361
  • 4
  • 17