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
  • 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