morfizm (morfizm) wrote,
morfizm
morfizm

SQL weirdness explained: how "WHERE ... NOT IN" can return empty set

I’ve just learned a really weird and unobvious thing (actually had to e-mail my question to SQL experts, suspecting that there is a bug in SQL Server), which will make me very cautious about using "WHERE … NOT IN".

Long story short, if you run:

 

SELECT i

FROM (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3) AS X

WHERE i NOT IN (3, 4, 5)

 

You’ll get: 1, 2
But if you run:

 

SELECT i

FROM (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3) AS X

WHERE i NOT IN (3, 4, 5, NULL)

 

You’ll get empty set.

 

The reason it happens is because

 

WHERE i NOT IN (3, 4, 5, NULL)

 

is being evaluated as

 

WHERE i <> 3 AND i <> 4 AND i <> 5 AND i <> NULL

 

Now since i <> NULL is false, the whole expression becomes false (even if i was NULL, NULL <> NULL would still evaluate to NULL, because you have to compare for nulls with IS NULL operator.

 

This difference (whether you have NULL or not in subquery) can be subtle and hard to notice in case if you query over a table.

The error-prone solution would be to use LEFT OUTER JOIN ON T and T.key IS NULL condition:

SELECT X.i

FROM (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3) AS X

LEFT OUTER JOIN (SELECT 3 AS i UNION SELECT 4

UNION SELECT 5 UNION SELECT NULL) AS Y

ON X.i = Y.i

WHERE Y.i IS NULL

This will return: 1, 2

Subscribe

  • Песня о спорте от Пайка

    Я не люблю тупые перепосты (ни видеть, ни делать), так что добавлю пару слов от себя. Пайк (pike_the_great) - мой давний френд, с…

  • Уличный концерт в Киеве - Вивальди, Времена года

    Постепенно разбираю накопленные фото и видео. Это с моей поездки в Украину летом 2019 года (запись сделана 2 июля). Совершенно чудесный концерт, на…

  • Обнулились!

    Песни Слепакова как хороший коньяк - со временем становятся всё лучше (т.е. актуальнее и современнее!) Вот эта, скажем, вообще крутяк для 2020. Хотя…

  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 8 comments