2

These are rather basic statements. I have a list of graphics which are linked to items in another table. I want to check how many of the graphics are not in use and can theoretically be deleted.

So first I used the NOT IN clause:

SELECT [GraphicNr]
      ,[Graphicfile]
  FROM [dbo].[Graphic]
 WHERE graphicnr NOT IN (SELECT graphicnr FROM dbo.Komp)

Which gave zero results, which seemed weird to me. After rewriting it to a NOT EXISTS, I got about 600 results:

SELECT [GraphicNr]
      ,[Graphicfile]
  FROM [dbo].[Graphic] a
 WHERE NOT EXISTS (SELECT graphicnr FROM dbo.komp b WHERE a.GraphicNr = b.GraphicNr)

So I guess I don't really have a problem, since the second statement works, but to my understanding, shouldn't the first one give the same results?

Salman A
  • 262,204
  • 82
  • 430
  • 521
Dabrush
  • 31
  • 1
  • 1
    The `(select graphicnr from dbo.Komp)` sub-query returns a null value. `NOT IN null-value` returns 0 rows. Lesson (learned): avoid `NOT IN (sub-query)`, do `NOT EXISTS` instead. – jarlh Aug 27 '18 at 12:15
  • Thank you! This makes sense, though I don't really understand why NOT IN NULL returns 0 rows by default. It's not a statement I've used much before, so I didn't know that NOT IN was this inferior. – Dabrush Aug 27 '18 at 12:19
  • Possible duplicate of [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) – Michał Turczyn Aug 27 '18 at 12:21
  • `select graphicnr from dbo.Komp where graphicnr is not null` would do it, but see answers below to understand _why_. – Salman A Aug 27 '18 at 12:33

2 Answers2

3

NOT IN with a subquery has strange behavior. If any row in the subquery returns a NULL value, then no rows are returned. This is due to following the strict semantics of NULL (which means: "I don't know if they are equal").

NOT EXISTS behaves as you would expect. For this reason, I recommend never using NOT IN with a subquery. Always use NOT EXISTS.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

That because of NULL value returned from subquery :

SELECT [GraphicNr], [Graphicfile]
FROM [dbo].[Graphic]
WHERE graphicnr NOT IN (SELECT graphicnr FROM dbo.Komp)

This would produce no records or no rows affected because of graphicnr not in (null) which is not desired output.

So, the NOT EXISTS would not work as the way the IN clause or NOT IN work. It behaves differently then IN or NOT IN clause.

However, you can prevent this by using IS NOT NULL filter in subquery. But the recommended way is to use NOT EXISTS instead.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52