
In
this article we are going to look at the similarities and differences
between the Oracle DECODE function and the CASE statement. Some people
say that the CASE statement is a better implementation of the DECODE
function, and that “whatever you can do with a DECODE you can do with a
CASE expression.So, let us dive into some details and see what we can find out…
Introduction
The
DECODE function has been around for a while, while the CASE statement
was introduced in Oracle 8.1.6. The CASE statement was supposed to be
superior, since it was introduced to be:
- Easier to read
- More flexible
- Compatible with the ANSI standard
Still,
as in almost everything when it comes to software developers, there are
different “camps”, with different opinions about what is the best. In
this article I will provide a lot of different information about the
similarities, as well as the differences between DECODE and CASE.
Hopefully, at the end of this article you will have enough information
to make up what to choose in different SQL scenarios.
So, let’s start digging into the details…
Please note (if it should matter) that I am using an 11g R2 database in the samples in this article.
The Oracle DECODE function
Since the DECODE function has been around the longest time, let us start with it.
DECODE syntax
According to the
Oracle Database SQL Language Reference, the DECODE syntax is as follows:
1 | DECODE (expression, search1-n, result1-n, default ) |
..where
search and
result operates in “pairs”, and there can be many
search/result ”pairs”.
..meaning that you i.e. can have a DECODE statement like this:
In
the latter sample I have deliberately formatted the DECODE statement
this way, since many of the complaints about the DECODE statement is
that it is easy to lose track when you have many
search/result “pairs”. In my opinion, the use of DECODE is all about proper formatting…and counting of amount of parameters.
Now, let us look at the different parameters in the syntax above.
- expression: This is the base expression that each search will be validated against
- search: This is the possible value for expression that we are checking on
- result: This is the value that is returned if search = expression
- default: This is the value returned by the DECODE function if none of the given search values are equal to expression. If none is given, the DECODE function returns NULL.
Please note the following:
- The components/arguments to the DECODE function can be any numeric or character types.
- The maximum number of components/parameters (all added up) in the DECODE function is 255.
- The expression value is checked against one search value at the time.
- In the Oracle DECODE function, a so-called “short-circuit evaluation” is used. That means that as soon as one of the search values matches the expression value, the next search value is NOT validated, but the result value of the matching search value is returned.
- DECODE can ONLY handle quality checks (“=”) between expression and search values
DECODE samples
Ok,
sometimes it can be easier to see some samples, than just looking at
the (at times confusing) syntax diagram. In the samples below, we are
going to use the tables that comes with the SCOTT sample schema in an
Oracle database installation. If you do not find these in your database,
there can be several reasons for it. Here are some of the most obvious
ones:
- The person setting up the database (most likely the
DBA) did not install the Oracle sample schemas (HR, SCOTT, etc.) when
the database was set up. They can be installed afterwards if you want
them. Speak with your DBA. For 11g R2 the installation of these schemas
can be found here: Oracle Database Sample Schemas.
- The sample schemas were installed, but they are by default locked, and needs to be unlocked by your DBA
At
this point I just wanted you to know that these samples are ONLY for
demonstration purposes. In a real life situation we would of course join
our tables, etc.
Example # 1: Setting name of department with DECODE
Let us pretend we did not have the DEPT (department table) in our schema, and we have the following query:
If we want to show the department in our query, we can use the DECODE statement like this:
14 | EMPNO ENAME DEPTNO DEPTNAME |
16 | 7369 SMITH 20 RESEARCH |
19 | 7566 JONES 20 RESEARCH |
22 | 7782 CLARK 10 ACCOUNTING |
23 | 7788 SCOTT 20 RESEARCH |
24 | 7839 KING 10 ACCOUNTING |
26 | 7876 ADAMS 20 RESEARCH |
29 | 7934 MILLER 10 ACCOUNTING |
Oracle DECODE limitations
It is important to point out a few limitations of the DECODE function.
Use of a range of values
As
mentioned earlier, the DECODE function ONLY handles equality
comparisons using the equal-to sign (“=”). If you i.e. wanted to add a
column in the last query above to say that the “ACCOUNTING” and the
“SALES” department were located in New York, and the “RESEARCH” and
“OPERATIONS” department were located in Boston, the only way you could
do this would be to handle each department code by itself, like so:
21 | EMPNO ENAME DEPTNO DEPTNAME CITY |
23 | 7369 SMITH 20 RESEARCH Boston |
24 | 7499 ALLEN 30 SALES New York |
25 | 7521 WARD 30 SALES New York |
26 | 7566 JONES 20 RESEARCH Boston |
27 | 7654 MARTIN 30 SALES New York |
28 | 7698 BLAKE 30 SALES New York |
29 | 7782 CLARK 10 ACCOUNTING New York |
30 | 7788 SCOTT 20 RESEARCH Boston |
31 | 7839 KING 10 ACCOUNTING New York |
32 | 7844 TURNER 30 SALES New York |
33 | 7876 ADAMS 20 RESEARCH Boston |
34 | 7900 JAMES 30 SALES New York |
35 | 7902 FORD 20 RESEARCH Boston |
36 | 7934 MILLER 10 ACCOUNTING New York |
Oracle DECODE and ANSI standard
The
DECODE function in Oracle is a native oracle thingy. If you i.e. would
like to convert your queries to MS SQL Server (uuh…please lean forwards
so that I can smack you..

), you would not be able to use this function. You would have to turn
it into a CASE statement, since the CASE statement IS compatible with
the ANSI standard.
Oracle DECODE and PL/SQL
The DECODE function can not be used within the PL/SQL language. What this means, is that if you i.e. have a
PL/SQL cursor (implicit
or explicit), you will not be able to use the DECODE function. The only
way you could use the DECODE function in PL/SQL, would be if you
defined a SQL query and used Dynamic SQL.
The Oracle CASE statement/expression
Ok, let’s go on to the next step in our comparison article: The CASE statement.
The CASE statement syntax
When it comes to the CASE statement in Oracle, there are actually two types of CASE statements:
- Simple CASE expression
- Searched CASE expression
Simple CASE expression
2 | WHEN comparison_expression_1 THEN return_expression_1 |
3 | WHEN comparison_expression_2 THEN return_expression_2 |
The simple CASE expression is used when you have one expression that you want to compare against a set of possible values.
In the simple CASE expression, please note the following:
- The
expression and the comparison expression must have the same data type
(for character data type), or have a numeric data type. For numeric data
types the common data type for the expressions are
determined/implicitly converted by the argument with the highest
numeric precedence
- Oracle will search for the first comparison expression that is equal to the expression.
- The comparison expressions must all be related
Searched CASE expression
2 | WHEN condition1 THEN return_expression1 |
3 | WHEN condition2 THEN return_expression2 |
The
searched CASE expression would be used i.e. in a query where you have
i.e. ranged values, conditions that are not related to each other, a
condition that is actually a sub query, etc.
In the searched CASE expression, please note the following:
- The comparison conditions do not have to be related (explained in sample below)
- Oracle will search left to right to find an occurrence of a condition that is TRUE. Then the corresponding return expression will be returned.
Common for both simple and searched type CASE expression:
- With Oracle CASE , a so-called “short-circuit evaluation” is used. That means that as soon as one of the comparison condition matches the expression value, the next comparison condition is NOT validated, but the return expression of the matching search expression is returned.
- If none of the conditions are TRUE, default will be returned.
- If default is not specified, NULL will be returned
- The maximum number of components/expressions (all added up) in the CASE is 255.
- All of the return expressions must
have the same data type (for character data type), or have a numeric
data type. For numeric data types the common data type for the
expressions are determined/implicitly converted by the argument with the
highest numeric precedence
Oracle CASE expression samples
Finally…here are some examples of usage of the CASE expression.
CASE sample # 1: Simple CASE expression
When
we discussed the DECODE function earlier, we had a sample where we
wanted to show the department name. Let us use the same scenario in this
CASE example:
5 | 5 WHEN 10 THEN 'ACCOUNTING' |
6 | 6 WHEN 20 THEN 'RESEARCH' |
8 | 8 WHEN 40 THEN 'OPERATIONS' |
13 | EMPNO ENAME DEPTNO DEPTNAME |
15 | 7369 SMITH 20 RESEARCH |
18 | 7566 JONES 20 RESEARCH |
21 | 7782 CLARK 10 ACCOUNTING |
22 | 7788 SCOTT 20 RESEARCH |
23 | 7839 KING 10 ACCOUNTING |
25 | 7876 ADAMS 20 RESEARCH |
28 | 7934 MILLER 10 ACCOUNTING |
As we can see, the same result as with DECODE.
CASE sample # 2: Searched CASE expression with range
Ok,
the DECODE function kept up on the flat parts…now let’s start going
uphill…. Let us now take the second scenario described under the DECODE
samples above, where we wanted to show the city/location for the
different departments:
5 | 5 WHEN 10 THEN 'ACCOUNTING' |
6 | 6 WHEN 20 THEN 'RESEARCH' |
8 | 8 WHEN 40 THEN 'OPERATIONS' |
11 | 11 WHEN deptno IN (10, 30) THEN 'New York' |
12 | 12 WHEN deptno IN (20, 40) THEN 'Boston' |
17 | EMPNO ENAME DEPTNO DEPTNAME CITY |
19 | 7369 SMITH 20 RESEARCH Boston |
20 | 7499 ALLEN 30 SALES New York |
21 | 7521 WARD 30 SALES New York |
22 | 7566 JONES 20 RESEARCH Boston |
23 | 7654 MARTIN 30 SALES New York |
24 | 7698 BLAKE 30 SALES New York |
25 | 7782 CLARK 10 ACCOUNTING New York |
26 | 7788 SCOTT 20 RESEARCH Boston |
27 | 7839 KING 10 ACCOUNTING New York |
28 | 7844 TURNER 30 SALES New York |
29 | 7876 ADAMS 20 RESEARCH Boston |
30 | 7900 JAMES 30 SALES New York |
31 | 7902 FORD 20 RESEARCH Boston |
32 | 7934 MILLER 10 ACCOUNTING New York |
There you go.
CASE sample # 3: Searched CASE expression inside a SUM
In
this next sample we are using the CASE expression in a way that I very
often use it in my line of work. We now want to summarize the salary for
each of the departments and then group them into their
cities/locations. So, basically, we want to figure out how much salary
is payed out in any of our two locations.
As a step # 1, we are
going to just divide the salaries into the different cities/locations
that the different departments reside:
5 | 5 WHEN deptno IN (10, 30) THEN 'New York' |
6 | 6 WHEN deptno IN (20, 40) THEN 'Boston' |
9 | 9 WHEN deptno IN (10, 30) THEN NVL(sal, 0) |
11 | 11 END new_york_salary |
13 | 13 WHEN deptno IN (20, 40) THEN NVL(sal, 0) |
19 | EMPNO ENAME DEPTNO CITY NEW_YORK_SALARY BOSTON_SALARY |
21 | 7369 SMITH 20 Boston 0 800 |
22 | 7499 ALLEN 30 New York 1600 0 |
23 | 7521 WARD 30 New York 1250 0 |
24 | 7566 JONES 20 Boston 0 2975 |
25 | 7654 MARTIN 30 New York 1250 0 |
26 | 7698 BLAKE 30 New York 2850 0 |
27 | 7782 CLARK 10 New York 2450 0 |
28 | 7788 SCOTT 20 Boston 0 3000 |
29 | 7839 KING 10 New York 5000 0 |
30 | 7844 TURNER 30 New York 1500 0 |
31 | 7876 ADAMS 20 Boston 0 1100 |
32 | 7900 JAMES 30 New York 950 0 |
33 | 7902 FORD 20 Boston 0 3000 |
34 | 7934 MILLER 10 New York 1300 0 |
As
you can see, the employee’s salary is shown in the city column for
where the department exists. Also, please note that I put an NVL around
the
sal field to avoid any summarization issues if one of the
values should be NULL. I would not work for free, but I guess someone
can…
The next and last step is to do the actual summarization:
2 | 2 WHEN deptno IN (10, 30) THEN NVL(sal, 0) |
6 | 6 WHEN deptno IN (20, 40) THEN NVL(sal, 0) |
12 | NEW_YORK_SALARY BOSTON_SALARY |
We have here used a ranged CASE expression within an aggregated SUM function.
CASE sample # 4: Searched CASE expression with non-related conditions
In
this example we are going to put together a query where we are going to
use a CASE expression where the conditions are not directly related to
each other.
Here is the scenario for this example:
- If the job title is “PRESIDENT” or “MANAGER”, we do not want to disclose if they have received a commission or not.
- If an employee (other than president and mangers) has received a commission, show the value 1
- Otherwise, show value 0
SQL> SELECT e.empno
4 | 5 WHEN job IN ( 'PRESIDENT' , 'MANAGER' ) THEN -1 |
5 | 6 WHEN ( comm IS NOT NULL AND comm > 0) THEN 1 |
11 | EMPNO ENAME JOB COMMISSION |
17 | 7654 MARTIN SALESMAN 1 |
21 | 7839 KING PRESIDENT -1 |
22 | 7844 TURNER SALESMAN 0 |
CASE sample # 5: Searched CASE expression with sub query
In
this last CASE example we are going to write a query where we are going
to present a list of the departments, and then show a 1 (the number
one) if that department has received a commission.
7 | 7 WHERE e.deptno = d.deptno |
8 | 8 AND e.comm IS NOT NULL |
12 | 12 END received_commission |
16 | DEPTNO DNAME RECEIVED_COMMISSION |
Handling of NULL values when USING CASE and DECODE
A last thing I would like to show, is the difference in the way that NULL is handled by CASE and DECODE:
15 | 2 WHEN NULL THEN 'NULL' |
As
you can see above, the result is different for the two. This is one of
the things that almost always seem to be mentioned when DECODE and CASE
differences are discussed. I put it here so that you can see it for
yourself.
In conclusion
Ok, so in this article
we have discussed some similarities, and some differences between the
Oracle DECODE function, the simple CASE expression, and the searched
CASE expression.
Personally I tend to use the searched CASE
expression, even the times I COULD use a simple CASE expression. I guess
it has to do with keeping the same standard of writing my CASE
statements. I like uniformity and structure in my SQL queries, as well
as in my PL/SQL code.
There are times when I use the DECODE function, but that would be only when there are very simple data comparisons.
There
are people that try their best to stick to the DECODE function whenever
they can, but as you probably have noticed…this article can be
concluded in the following way: A CASE expression can replace a DECODE
function any time, and it has a lot of additional functionality as well.
The opposite is not the case (

).
On
thing you might find missing in this article is the differences in
performance. I have personally not done any big comparisons, but after
what I have been reading from experts on the Internet: There should not
be very much difference. Also, in my work I use the CASE expressions 99%
of the times, since I very often have conditions that are non-related,
or have a sub-query within it.
No comments :
Post a Comment