?

Log in

No account? Create an account
   Journal    Friends    Archive    Profile    Memories
 

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


Oct. 18th, 2009 12:03 am 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

8 comments - Leave a commentPrevious Entry Share Next Entry

Comments:

From:_m_e_
Date:October 18th, 2009 09:43 am (UTC)
(Link)
А может писать -
select * from A 
where A.X not in 
	(select X from B 
	where X is not null)


Ну и конечно всегда есть грязный хак
set ansi_nulls off

select * from A 
where A.X not in (select X from B)
From:morfizm
Date:October 18th, 2009 09:50 am (UTC)
(Link)
Первый вариант - хорошо, мне он тоже в голову пришёл.

Второй - дык, на MSDN-е же пишут, что в будущем это работать перестанет. Это совсем уж грязный хак :)

Но вообще это жесть.
По моим понятиям

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

нужно считать как

WHERE NOT (i = 3 OR i = 4 OR i = 5 OR i = NULL)

в этом случае всё бы работало правильно, т.е. как настоящий "NOT (i IN (... ))".

В SQL даже "NOT (i IN (... ))" не работает, т.к. он, по-видимому, оптимизирует его, превращая в NOT IN, а дальше пошло-поехало, пляска с NULL-ами.
From:_m_e_
Date:October 18th, 2009 11:00 am (UTC)
(Link)
последнее - это не оптимизатор, а ansi:
NOT (UNKNOWN) == UNKNOWN
NOT(NULL = NULL) == NULL = NULL == UNKNOWN
From:morfizm
Date:October 18th, 2009 11:27 am (UTC)
(Link)
Хм. А что насчёт FALSE OR UNKNOWN - неужели = UNKNOWN?
From:_m_e_
Date:October 18th, 2009 09:36 pm (UTC)
(Link)
From:tanu_atyasowa
Date:October 18th, 2009 07:18 pm (UTC)
(Link)
класссно!
ничего не поняла :-)
From:morfizm
Date:October 18th, 2009 07:29 pm (UTC)
(Link)
Это рабочее. SQL сервер :)

К сожалению, я не могу делать публичные посты для разных аудиторий. Все френды будут видеть всё равно.
From:tanu_atyasowa
Date:October 18th, 2009 08:03 pm (UTC)
(Link)
а я все равно ничего не поняла))) но набор букв классный)))