Menu
Is there an ANSI SQL equivalent to Oracle's DECODE function?
Oracle's decode function is the IF-THEN-ELSE construct in SQL.
790
The CASE Statement The expression is used to compare against. Many conditions and results can be specified, and if a condition matches the expression, then the result is returned. The ELSE keyword specifies what happens if no condition is met. It was introduced into Oracle to replace the DECODE function.
If not matches are found, the DECODE function will return default. If default is omitted, then the DECODE function will return null (if no matches are found). With that being said, CASE is a better function as it is simpler to write and can be used in PL/SQL. CASE is easier to read, understand and maintain the code.
The DECODE function can be used in Oracle/PLSQL.
From performance perspective, In Oracle decode and CASE does not make any difference. But in Exadata , Decode is faster than CASE. The Decode operation is done at storage Server level where the data is present BUT CASE is done at DB Instance level which receives data from DB storage Level.
Please note that Oracle DECODE treats null as equal to null, while CASE(and any other comparisons) don't.
Example: Decode(a,b,1,0) will return 1 if both a and b are nulls.
Just run these 2 statements to see the difference.
select case null when null then 'Y' else 'N' end dd from dual;
select decode(null, null, 'Y', 'N') dd from dual;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
