[SQL] NOT IN clause and NULL values

from：http://stackoverflow.com/questions/129077/not-in-clause-and-null-values


當下NOT IN時，括號裡面的值含有NULL，就會甚麼都撈不到

因為null不等於任何值

null也不等於null

所以，下NOT IN時，建議將null過濾掉 WHERE *** IS NOT NULL

A: select 'true' where 3 in (1, 2, 3, null)
B: select 'true' where 3 not in (1, 2, null)

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

zh-CN → zh-TW

，下