How CASE expression manipulates with NULL in Oracle SQL

My next post is an answer to the below question about Oracle SQL CASE expression and the way how it manipulates with NULL values compared to DECODE function. This topic in fact is very common in Oracle database developers’ area.

===============
Hi Kirill,

Could you please help me to clarify below mention doubt.

DECODE:-

select decode (null,null,’true’,’false’) as value from dual

OUTPUT:- TRUE

CASE:-

select case null when null then ‘true’ else  ‘false’ end as Case_Test from dual

OUTPUT :- FALSE

Why above mention queries are giving different output  and what is the difference between case and decode?

==============

CASE expression treats NULLs in Oracle database SQL a bit differently compared to DECODE. You have to understand that the Boolean expressions usually result in TRUE or FALSE , but NULLs introduce a third possible result which is : UNKNOWN . At the same time NULL is not the same as UNKNOWN. See an example below:

sum + NULL results in NULL (this is a Scalar expression.)
NULL = NULL results in UNKNOWN (this is a Boolean expression.)
sum < NULL results in UNKNOWN (this is a Boolean expression.)

Ok. Now let’s explore the Oracle SQL CASE expression itself.

CASE introduces two ways of conditional expressions: Simple CASE and Searched CASE. Read more »