In search condition

Question

Possible Duplicate:
SQL NOT IN constraint and NULL values

Why is following query not returning hello?

select 'hello' where 'a' not in ('b', null)

Answer 1

I'm not sure about the lack of a table, but your list includes a NULL and so the SQL engine doesn't know whether or not 'a' is in the list.

Answer 2

The short answer is you can't have a null value.

Answer 3

Your query can be expanded to:

SELECT 'hello' WHERE 'a' <> 'b' AND 'a' <> NULL;

The first condition evaluates to true.

The second condition evaluates to neither true nor false because NULL is neither equal nor unequal to anything. The full WHERE clause is then: "true AND neither true nor false".