Are a CASE statement and a DECODE equivalent?
It seems like the simple CASE expression and the DECODE function are equivalent and that the results returned by them should be identical. Are they?
The documentation has the following to say about the simple CASE expression:
The simple CASE expression returns the first result for which selector_value matches selector. Remaining expressions are not evaluated. If no selector_value matches selector, the CASE expression returns else_result if it exists and NULL otherwise.
Comparing this to the DECODE function, the descriptions seem to be identical.
DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.
As the searched CASE expression can be equivalent to the simple, this could be construed to be the same as well.
These three statements all seem to return the same result, 0.
select case 1 when 2 then null else 0 end as simple_case
, case when 1 = 2 then null else 0 end as searched_case
, decode(1, 2, null, 0) as decode
from dual
Do the simple CASE expression and the DECODE function (and in specific circumstances the searched CASE expression) always return the same result?
Short answer, no.
The slightly longer answer is nearly.
It only appears that the result obtained from each statement is identical. If we use the DUMP function to evaluate the data types returned you'll see what I mean:
SQL> select dump(case 1 when 2 then null else 0 end) as simple_case
2 , dump(case when 1 = 2 then null else 0 end) as searched_case
3 , dump(decode(1, 2, null, 0)) as decode
4 from dual;
SIMPLE_CASE SEARCHED_CASE DECODE
------------------ ------------------ -----------------
Typ=2 Len=1: 128 Typ=2 Len=1: 128 Typ=1 Len=1: 48
SQL Fiddle
You can see that the data type of the DECODE is 1, whereas the two CASE statements "return" a data type of 2. Using Oracle's Data Type Summary, DECODE is returning a VARCHAR2 (data type 1) whereas the CASE statements are "returning" numbers (data type 2).
I assume this occurs because, as the names suggest, DECODE is a function and CASE isn't, which implies they have been implemented differently internally. There's no real way to prove this.
You might think that this doesn't really affect anything. If you need it to be a number Oracle will implicitly convert the character to a number under the implicit conversion rules, right? This isn't true either, it won't work in a UNION as the data types have to be identical; Oracle won't do any implicit conversion to make things easy for you. Secondly, here's what Oracle says about implicit conversion:
Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:
SQL statements are easier to understand when you use explicit data type conversion functions.
Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.
Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.
Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.
That's not a pretty list; but the penultimate point brings me nicely on to dates. If we take the previous query and convert it into one that uses a date instead:
select case sysdate when trunc(sysdate) then null
else sysdate
end as simple_case
, case when sysdate = trunc(sysdate) then null
else sysdate
end as searched_case
, decode(sysdate, trunc(sysdate), null, sysdate) as decode
from dual;
Once again, using DUMP on this query the CASE statements return data type 12, a DATE. The DECODE has converted sysdate into a VARCHAR2.
SQL> select dump(case sysdate when trunc(sysdate) then null
2 else sysdate
3 end) as simple_case
4 , dump(case when sysdate = trunc(sysdate) then null
5 else sysdate
6 end) as searched_case
7 , dump(decode(sysdate, trunc(sysdate), null, sysdate)) as decode
8 from dual;
SIMPLE_CASE
----------------------------------
Typ=12 Len=7: 120,112,12,4,22,18,7
SEARCHED_CASE
----------------------------------
Typ=12 Len=7: 120,112,12,4,22,18,7
DECODE
----------------------------------
Typ=1 Len=19: 50,48,49,50,45,49,50,45,48,52,32,50,49,58,49,55,58,48,54
SQL Fiddle
Note (in the SQL Fiddle) that the DATE has been converted into a character using the sessions NLS_DATE_FORMAT.
Having a date that's been implicitly converted into a VARCHAR2 can cause problems. If you're intending to use TO_CHAR, to convert your date into a character, your query will break where you're not expecting it.
SQL> select to_char( decode( sysdate
2 , trunc(sysdate), null
3 , sysdate )
4 , 'yyyy-mm-dd') as to_char
5 from dual;
select to_char( decode( sysdate
*
ERROR at line 1:
ORA-01722: invalid number
SQL Fiddle
Equally, date arithmetic no longer works:
SQL>
SQL>
SQL> select decode(sysdate, trunc(sysdate), null, sysdate) + 1 as decode
2 from dual;
select decode(sysdate, trunc(sysdate), null, sysdate) + 1 as decode
*
ERROR at line 1:
ORA-01722: invalid number
SQL Fiddle
Interestingly DECODE only converts the expression to a VARCHAR2 if one of the possible results is NULL. If the default value is NULL then this doesn't happen. For instance:
SQL> select decode(sysdate, sysdate, sysdate, null) as decode
2 from dual;
DECODE
-------------------
2012-12-04 21:18:32
SQL> select dump(decode(sysdate, sysdate, sysdate, null)) as decode
2 from dual;
DECODE
------------------------------------------
Typ=13 Len=8: 220,7,12,4,21,18,32,0
SQL Fiddle
Note that the DECODE has returned a data type of 13. This isn't documented but is, I assume, a type of date as date arithmetic etc. works.
In short, avoid DECODE if you possibly can; you might not necessarily get the data types you're expecting. To quote Tom Kyte:
Decode is somewhat obscure -- CASE is very very clear. Things that are easy to do in decode are easy to do in CASE, things that are hard or near impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.
Just to be complete there are two functional differences between DECODE and CASE.
- DECODE cannot be used within PL/SQL.
-
CASE cannot be used to compare nulls directly
SQL> select case null when null then null else 1 end as case1 2 , case when null is null then null else 1 end as case2 3 , decode(null, null, null, 1) as decode 4 from dual 5 ; CASE1 CASE2 DECODE ---------- ---------- ------ 1SQL Fiddle
Ben has written a lengthy answer on the differences between DECODE and CASE. He demonstrates that DECODE and CASE may return different datatypes for apparently the same set of values without properly explaining why this happens.
DECODE() is quite prescriptive: it is always the datatype of the first result parameter. Oracle applies implicit conversion to all the other result parameters. It will throw an error , if (say) the first result parameter is numeric and the default value is a date.
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
This is described in the documentation: find out more.
In the first scenario the first result parameter is NULL, which Oracle decides to treat as VARCHAR2. If we change it so that the first result parameter is numeric and the default value is null the DECODE() statement will return a NUMBER; a DUMP() proves that this is so.
Whereas CASE insists that all the returned values have the same datatype, and will throw a compilation error if this is not the case. It won't apply implicit conversion. This is also covered in the documentation. Read it here.
The difference boils down to this. The following DECODE statement will run, the CASE statement won't:
select decode(1, 1, 1, '1') from dual;
select case 1 when 1 then 1 else '1' end from dual;
Obligatory SQL Fiddle.