Others have explained the technicality of why you get the result - I
would just like to point out that you should be careful of influencing
your own expectation with the words you use in these statements.
SQL only pretends to understand English, it really is a
mathematical/logical language and the words are mnemonics for
operators/tokens rather than meaningful in dictionary terms.
Here's an example that is even less intuitive than yours to prove the point:
SELECT CASE NULL WHEN NULL THEN 'This value is NULL' ELSE 'This
value is definitely not Null' END;
Answer:
This value is definitely not Null
My choice of words only serve to confuse - it's best to understand by
rewriting the statement like this:
SELECT CASE WHEN NULL IS NULL THEN 'This value is NULL' ELSE 'This
value is definitely not Null' END;
Which of course correctly returns:
This value is Null
(because "Null is Null" returns a boolean value and not a Null) and will
return correct results when used in a normal statement like:
SELECT CASE WHEN X IS NULL THEN 'X is NULL' ELSE 'X is not Null'
END;
While this statement:
SELECT CASE X WHEN NULL THEN 'X is NULL' ELSE 'X is definitely
not Null' END;
Will short-circuit to the ELSE as soon as X is actually NULL, and so
will return "X is definitely not Null" in every conceivable scenario -
as it should in mathematical/logical terms.
Cheers,
Ryan
On 2016/02/17 5:43 AM, Richard Hipp wrote:
> On 2/16/16, Denis Burke <burkeden at gmail.com> wrote:
>> select case when null not in ('a') then 'not in list' else 'is in list'
>> end;
>>
> The expression "null not in ('a')" evaluates to null. So your query
> is really: "select case when null then 'not-in-list' else
> 'is-in-list' end;" Since the condition is not true (because null is
> not true) then the else clause is taken. This is correct behavior.
>
> PostgreSQL gets the same answer.
