-1

Code example to run:

DECLARE @IdsTable AS TABLE(Id INT);
INSERT INTO @IdsTable VALUES (NULL), (1);

SELECT * FROM @IdsTable WHERE Id IS NOT NULL; -- 1
SELECT * FROM @IdsTable WHERE Id NOT IN (NULL); -- 2
SELECT * FROM @IdsTable WHERE Id != NULL; -- 3

Why do queries 2 and 3 not return a record where Id is 1?

Larnu
  • 76,706
  • 10
  • 34
  • 63
Basil Kosovan
  • 846
  • 1
  • 7
  • 21
  • FYI, spell checking your content goes a long way to getting your question to be well received. – Larnu May 23 '22 at 13:11
  • 2
    To answer the question `NULL` does (not) equal anything, including `NULL`; `NULL != {Non NULL Value}` = `UNKNOWN` not `TRUE`. `NOT IN (List containing NULL)` = `UNKNOWN` too. So `1 NOT IN (3,4,NULL,6)` would be `UNKNOWN`; this is well documented behaviour and is why it is strongly recommended that `NOT EXISTS` is used rather than `NOT IN` when `NULL` values could be present. – Larnu May 23 '22 at 13:13
  • 1
    Searching for your question title would have told you everything you need to know – HoneyBadger May 23 '22 at 13:15

0 Answers0