While the decode operator has been around since the earliest days
of Oracle, the case operator was introduced in Oracle 8.1.6 as a standard, more
meaningful and more powerful.
In terms of usage, case and decode both are used like to
accomplish the "if then else" statements.
Apart from the syntactical differences:
·
DECODE works with expressions
which are scalar values, CASE can work with predicates and sub queries in
searchable form.
·
In decode we can’t use for range (like
2000 to 4000 etc.) but case we can.
·
Oracle also boasts that CASE
executes faster in the optimizer than does DECODE.
·
DECODE can be used Only inside SQL
statement.
·
But CASE can be used any where
even as a parameter of a function/procedure.
·
Both differ in the way NULL is
handled in the expression.
The case function is a
much better option because case is:
Ø More flexible than DECODE.
Ø Easier to read.
Ø ANSI-compatible.
However, CASE is essentially a better implementation
of DECODE so the reasons for using either are similar.
The decode function is considered the most powerful function in
Oracle. Oracle 8i release introduced the CASE expression. The CASE expression
can do all that DECODE does plus lot of other things including IF-THEN
analysis, use of any comparison operator and checking multiple conditions, all
in a SQL query itself.
Moreover, using the CASE function, multiple conditions provided in
separate SQL queries can be combined into one, thus avoiding multiple
statements on the same table.
Sr. No.
|
CASE
|
DECODE
|
1.
|
CASE can work with logical operators
other than ‘=’ , CASE is
capable of other logical comparisons such as < ,> ,BETWEEN , LIKE etc.
|
DECODE performs an equality check only.
|
2.
|
CASE can work with predicates and
searchable sub queries.
|
DECODE works with expressions that are scalar values only.
|
3.
|
CASE can work as a PL/SQL
construct, CASE can be an efficient substitute for IF-THEN-ELSE
in PL/SQL.
|
DECODE can work as a function inside SQL only.
|
4.
|
CASE can be used as parameter of a
function/procedure.
|
DECODE cannot.
|
5.
|
CASE expects data type consistency.
|
DECODE does not.
|
6.
|
CASE handles NULL differently.
|
In DECODE, Oracle consider two NULL to be equivalent.
|
7.
|
CASE complies with ANSI SQL.
|
DECODE is proprietary to Oracle.
|
8.
|
CASE executes faster in the Optimizer
than does DECODE.
|
|
9.
|
CASE is a statement.
|
DECODE is a function.
|
Detailed
examples:
1. CASE can
work with logical operators other than ‘=’
SELECT e.first_name||' '||e.last_name Name,
CASE
WHEN e.salary < 10000 THEN
'Grade I'
WHEN (e.salary >= 10000 AND e.salary < 20000) THEN
'Grade II'
WHEN (e.salary >= 20000 AND e.salary < 30000) THEN
'Grade III'
ELSE
'Grade IV'
END sal_grade
FROM employees e
WHERE rownum < 5;
NAME
|
SAL_GRADE
|
Steven King
|
Grade III
|
Neena Kochhar
|
Grade II
|
Lex De Haan
|
Grade II
|
Alexander Hunold
|
Grade I
|
2. CASE
can work with predicates and searchable sub queries
SELECT e.first_name || ' ' || e.last_name Name,
CASE
-- predicate with "in"
-- set the category based on last_name list
WHEN e.last_name IN ('King', 'Smith', 'Ward') THEN
'Top Bosses'
-- searchable subquery
-- identify if this employee has a reportee
WHEN EXISTS (SELECT 1
FROM employees emp1
WHERE emp1.manager_id =
e.employee_id) THEN
'Managers'
ELSE
'General Employees'
END emp_category
FROM employees e
WHERE rownum < 5;
NAME
|
EMP_CATEGORY
|
Steven King
|
Top Bosses
|
Neena Kochhar
|
Managers
|
Lex De Haan
|
Managers
|
Alexander Hunold
|
Managers
|
3. CASE
can work as a PL/SQL construct
DECLARE
grade CHAR(1);
BEGIN
grade := 'b';
CASE grade
WHEN 'a' THEN
dbms_output.put_line('excellent');
WHEN 'b' THEN
dbms_output.put_line('very good');
WHEN 'c' THEN
dbms_output.put_line('good');
WHEN 'd' THEN
dbms_output.put_line('fair');
WHEN 'f' THEN
dbms_output.put_line('poor');
ELSE
dbms_output.put_line('no such grade');
END CASE;
END;
/
Output
-------------------------------
very good
4. CASE
can be used as parameter of a function/procedure
CREATE OR REPLACE PROCEDURE proc_test(i NUMBER) AS
BEGIN
dbms_output.put_line('output = ' || i);
END;
/
BEGIN
proc_test(decode('THREE', 'THREE', 3, 0));
END;
Error
-------------------------------
ORA-06550: line 2, column
11:
PLS-00204: function
or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 2, column
1:
PL/SQL: Statement
ignored
BEGIN
proc_test(CASE 'THREE' WHEN 'THREE' THEN 3 ELSE 0
END);
END;
Output
-------------------------------
output = 3
5. CASE
expects data type consistency, DECODE does not
SELECT decode(2, 1, 1, '2', '2', '3') t FROM dual;
T
|
2
|
SELECT CASE 2
WHEN 1 THEN
1
WHEN '2' THEN
'2'
ELSE
'3'
END
FROM dual;
Error
-------------------------------
ORA-00932: inconsistent datatypes:
expected NUMBER got CHAR
6. CASE
handles NULL differently
SELECT DECODE(NULL, NULL, 'NULL', 'NOT NULL') NULL_TEST FROM DUAL;
NULL_TEST
|
NULL
|
SELECT CASE NULL
WHEN NULL THEN 'NULL'
ELSE 'NOT NULL'
END NULL_TEST FROM DUAL;
NULL_TEST
|
NOT NULL
|
SELECT CASE
WHEN NULL IS NULL THEN 'NULL'
ELSE 'NOT NULL'
END NULL_TEST
FROM DUAL;
NULL_TEST
|
NULL
|
7. The
difference in readability
In very simple situations, DECODE
is shorter and easier to understand than CASE.
Complicated logical comparisons in DECODE, even if
technically achievable, are a recipe for messy, bug-prone code. When the same
can be done more cleanly with CASE, go for CASE.
SELECT e.first_name || ' ' || e.last_name NAME,
decode(e.department_id,
80,
'Accounting',
90,
'Research',
100,
'Sales',
'Unknown') AS department
FROM employees e
WHERE rownum < 5;
NAME
|
DEPARTMENT
|
Steven King
|
Research
|
Neena Kochhar
|
Research
|
Lex De Haan
|
Research
|
Alexander Hunold
|
Unknown
|
SELECT e.first_name || ' ' || e.last_name NAME,
CASE e.department_id
WHEN 80 THEN
'Accounting'
WHEN 90 THEN
'Research'
WHEN 100 THEN
'Sales'
ELSE
'Unknown'
END AS department
FROM employees e
WHERE rownum < 5;
NAME
|
DEPARTMENT
|
Steven King
|
Research
|
Neena Kochhar
|
Research
|
Lex De Haan
|
Research
|
Alexander Hunold
|
Unknown
|
I hope you all have enjoyed reading this article.
Comments are welcome....
