SQL case giving inconsistent result

Question

I am getting an inconsistent result from below two SQL queries.

Query1 : select (case when 'Abc' = null then 1 else 0 end) from dual

Query2 : select (case when ('Abc' <> null) then 1 else 0 end) from dual

Result for both queries same i.e 0

What's wrong am I missing some thing ?

NOTE : : I know I can use IS NULL and IS NOT NULL but my question is why result from above queries is inconsistent.

EDIT : Added from answer of @ppeterka.

select (case when null = null then 1 else 0 end) from dual

This returns 0 too. Null is not even equal to itself.

But then what does this return?

select (case when null <> null then 1 else 0 end) from dual

0 again

SQLFiddle link

Answer 1

Because NULL is unknown that is why the result is 0. When you want to compare a column or a value if it is null or not, use IS NULL or IS NOT NULL.

select (case when 'Abc' IS null then 1 else 0 end) from dual       -- 0
select (case when ('Abc' IS NOT null) then 1 else 0 end) from dual -- 1

• SQLFiddle Demo

Answer 2

The result of comparing anything to NULL, even itself, is always NULL(not TRUE or FALSE).

Searched CASE expression:

• If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

In your case the result always will be 0, because 0 in ELSE clause

Answer 3

To add a bit of twist to this, try this out:

select (case when null = null then 1 else 0 end) from dual

This returns 0 too. Null is not even equal to itself.

But then what does this return?

select (case when null <> null then 1 else 0 end) from dual

This returns 0 again! Oh holy... It is not even not equal to itself, while it is not equal to itself... Quite a situation to grasp without getting insane...

Why to keep this all in mind? - one might ask

One example is indexing: in Oracle, indexes don't work the way one would expect them to work on NULL values where a column permits the use of that value. This means that given an index, if all the values (fields, functions on fields, etc) of a row that are included in the index are all NULL, that row won't be indexed in that given index. So this means that an index, that has only one value indexed (for example, a field directly), a null value would mean that row not to be included in the index.

To overcome this

• it might be advisable to add an index with a distinct, exact value semantically representing the NULL meaning, like NVL(mynullcol,-1) on a column only containing positive integers to be able to query them quickly.
• or you could add a constant value to form a "semi-multivalue" index, that indexes all rows, where one can be null, as the other is a constant. (create index idx_myindex on table(column_with_nulls,1); )

(This question and this article detail this subject in a bit more depth)

Another example is ordering...

select (case when null < null then 1 else 0 end) from dual;
select (case when null > null then 1 else 0 end) from dual;

Both 0. This is OK... We expected this by now... And what about this?

select (case when 'Abc' > null then 1 else 0 end) from dual;
select (case when null > 'Abc' then 1 else 0 end) from dual;

Uh-oh... Both 0 again. This might be an issue - how is ordering going to work?

select col_1 from
(select null as col_1 from dual)
union all (select 'Abc' as col_1 from dual)
union all (select null as col_1 from dual)
union all (select null as col_1 from dual)
order by col_1 

This however consistently returns:

Abc
null
null
null

Using ... order by col_1 DESC returns:

null
null
null
Abc

So from this, on an empirical basis, it does seem that 'Abc' < null... However, as per the valuable comment of @ypercube:

the sort order can be set with the NULLS LAST and NULLS FIRST modifiers (at least in Oracle). What you observe is the default sort order when the ORDER BY has no modifier

NULL is a twisted business, it is wise to steer away from it if it is possible... (And this is not only true for SQL, but for certain situations in OOP languages too.)

Answer 4

You can't use <> or = with nulls. You need to say

select (case when 'Abc' is null then 1 else 0 end) from dual

and

select (case when 'Abc' is not null then 1 else 0 ) from dual

Answer 5

It is not inconsistent. if you have 2 objects A,B then it's one of the following three:

• A equals B

• A not equals B

• you cannot compare A and B

It's like examining if (0/0 > 0) or (0/0 < 0) or (0/0 = 0). You just cannot compare them. every option is false

For your example: case checks if your argument is true

• argument ('abc'=null) is not true, it's null

• argument ('abc'<>null) is not true, it's null

Answer 6

The value NULL means the data value for the column is Unknown. A NULL is not synonymous with Zero, or zero length string or blank.

Anything you compare with NULL will result in Unknown (NULL).

Please check this to clear your doubts. To have the correct result, use IS NULL or IS Not NULL as you already know.