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.
Could you please help me to clarify below mention doubt.
select decode (null,null,’true’,’false’) as value from dual
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.
One is called simple CASE and operates like a switch statement.
In a simple CASE expression, Oracle Database searches for the first WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN … THEN pairs meet this condition and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns NULL. You cannot specify the literal NULL for every return_expr and the else_expr.
The simple CASE expressions use implicit equality comparisons which operate the same way as in WHERE clauses. Thus, a simple CASE expression cannot check for the existence of NULL directly. A check for NULL in a simple CASE expression always results in UNKNOWN and will never return a positive result like in the following example:
/*this will never be returned*/
SQL+> select case null when null then 'is not NULL, but UNKNOWN' end from dual;
Since the expression NULL = NULL produces UNKNOWN result, the string ‘is not NULL, but UNKNOWN’ will never be returned.
The second is called a Searched CASE and operates like an if…else if construction. Here you can use predicates like IS NULL and IS NOT NULL to properly compare NULLs.
In a searched CASE expression, Oracle database searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be TRUE, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns NULL.
The following example shows how to use a Searched CASE expression to properly check for NULL:
SQL+> variable i varchar2
SQL+> exec :i := NULL;
SQL+> select case when :i is null then 'true' else 'false' end as Case_Test from dual ;
To the question what is better to use DECODE or CASE I can add the following.
DECODE function was originally provided in earlier database releases. CASE expression was introduced in Oracle database 8.1.6 and was at the beginning in certain cases slower than DECODE. Later in 9i database Oracle improved the functionality and performance of CASE expression. So now you can achieve everything with CASE what DECODE can, and even in a more readable and logical way.
Hope this article about Oracle database SQL expression CASE can clarify differences in manipulating NULL values compared to DECODE function.