Oracle DECODE and CASE: What is the difference

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:

1	DECODE (    expression

2	,search1, result1

3	,search2, result2

4	,search3, result3

5

, default

6

)

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:

1.  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.
2. 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:

1	SQL> SELECT  e.empno

2	2         ,e.ename

3	3          ,deptno

4	4  FROM   emp e

5

5  ;

6

7	EMPNO ENAME      DEPTNO

8	----- ---------- ------

9	7369 SMITH          20

10	7499 ALLEN          30

11	7521 WARD           30

12	7566 JONES          20

13	7654 MARTIN         30

14	7698 BLAKE          30

15	7782 CLARK          10

16	7788 SCOTT          20

17	7839 KING           10

18	7844 TURNER         30

19	7876 ADAMS          20

20	7900 JAMES          30

21	7902 FORD           20

22	7934 MILLER         10

23

24	14 rows selected

If we want to show the department in our query, we can use the DECODE statement like this:

1	SQL> SELECT  e.empno

2	2         ,e.ename

3	3          ,deptno

4	4          ,DECODE(

5	5                      deptno

6	6                      ,10, 'ACCOUNTING'

7	7                      ,20, 'RESEARCH'

8	8                      ,30, 'SALES'

9	9                      ,40, 'OPERATIONS'

10	10          ) deptname

11	11  FROM   emp e

12

12  ;

13

14	EMPNO ENAME      DEPTNO DEPTNAME

15	----- ---------- ------ ----------

16	7369 SMITH          20 RESEARCH

17	7499 ALLEN          30 SALES

18	7521 WARD           30 SALES

19	7566 JONES          20 RESEARCH

20	7654 MARTIN         30 SALES

21	7698 BLAKE          30 SALES

22	7782 CLARK          10 ACCOUNTING

23	7788 SCOTT          20 RESEARCH

24	7839 KING           10 ACCOUNTING

25	7844 TURNER         30 SALES

26	7876 ADAMS          20 RESEARCH

27	7900 JAMES          30 SALES

28	7902 FORD           20 RESEARCH

29	7934 MILLER         10 ACCOUNTING

30

31	14 rows selected

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:

1	SQL> SELECT  e.empno

2	2         ,e.ename

3	3          ,deptno

4	4          ,DECODE(

5	5                      deptno

6	6                      ,10, 'ACCOUNTING'

7	7                      ,20, 'RESEARCH'

8	8                      ,30, 'SALES'

9	9                      ,40, 'OPERATIONS'

10	10          ) deptname

11	11          ,DECODE(

12	12                      deptno

13	13                      ,10, 'New York'

14	14                      ,20, 'Boston'

15	15                      ,30, 'New York'

16	16                      ,40, 'Boston'

17	17          ) city

18	18  FROM   emp e

19

19  ;

20

21	EMPNO ENAME      DEPTNO DEPTNAME   CITY

22	----- ---------- ------ ---------- --------

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

37

38	14 rows selected

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

1	CASE expression

2	WHEN comparison_expression_1 THEN return_expression_1

3	WHEN comparison_expression_2 THEN return_expression_2

4

..

5	--AND so ON

6

..

7	ELSE DEFAULT

8

END

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

1

CASE

2	WHEN condition1 THEN return_expression1

3	WHEN condition2 THEN return_expression2

4

..

5	--and so on

6

..

7	ELSE default

8

END

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:

1	SQL> SELECT  e.empno

2	2          ,e.ename

3	3          ,deptno

4	4          ,CASE deptno

5	5              WHEN 10 THEN 'ACCOUNTING'

6	6              WHEN 20 THEN 'RESEARCH'

7	7              WHEN 30 THEN 'SALES'

8	8              WHEN 40 THEN 'OPERATIONS'

9	9          END deptname

10	10  FROM   emp e

11

11  ;

12

13	EMPNO ENAME      DEPTNO DEPTNAME

14	----- ---------- ------ ----------

15	7369 SMITH          20 RESEARCH

16	7499 ALLEN          30 SALES

17	7521 WARD           30 SALES

18	7566 JONES          20 RESEARCH

19	7654 MARTIN         30 SALES

20	7698 BLAKE          30 SALES

21	7782 CLARK          10 ACCOUNTING

22	7788 SCOTT          20 RESEARCH

23	7839 KING           10 ACCOUNTING

24	7844 TURNER         30 SALES

25	7876 ADAMS          20 RESEARCH

26	7900 JAMES          30 SALES

27	7902 FORD           20 RESEARCH

28	7934 MILLER         10 ACCOUNTING

29

30	14 rows selected

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:

1	SQL> SELECT  e.empno

2	2          ,e.ename

3	3          ,deptno

4	4          ,CASE deptno

5	5              WHEN 10 THEN 'ACCOUNTING'

6	6              WHEN 20 THEN 'RESEARCH'

7	7              WHEN 30 THEN 'SALES'

8	8              WHEN 40 THEN 'OPERATIONS'

9	9          END deptname

10	10          ,CASE

11	11              WHEN deptno IN (10, 30) THEN 'New York'

12	12              WHEN deptno IN (20, 40) THEN 'Boston'

13	13          END city

14	14  FROM   emp e

15

15  ;

16

17	EMPNO ENAME      DEPTNO DEPTNAME   CITY

18	----- ---------- ------ ---------- --------

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

33

34	14 rows selected

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:

1	SQL> SELECT  e.empno

2	2         ,e.ename

3	3          ,deptno

4	4          ,CASE

5	5              WHEN deptno IN (10, 30) THEN 'New York'

6	6              WHEN deptno IN (20, 40) THEN 'Boston'

7	7          END city

8	8         ,CASE

9	9              WHEN deptno IN (10, 30) THEN NVL(sal, 0)

10	10              ELSE 0

11	11          END new_york_salary

12	12         ,CASE

13	13              WHEN deptno IN (20, 40) THEN NVL(sal, 0)

14	14              ELSE 0

15	15          END boston_salary

16	16  FROM   emp e

17

17  ;

18

19	EMPNO ENAME      DEPTNO CITY     NEW_YORK_SALARY BOSTON_SALARY

20	----- ---------- ------ -------- --------------- -------------

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

35

36	14 rows selected

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:

1	SQL> SELECT  SUM(CASE

2	2              WHEN deptno IN (10, 30) THEN NVL(sal, 0)

3	3              ELSE 0

4	4          END) new_york_salary

5	5         ,SUM(CASE

6	6              WHEN deptno IN (20, 40) THEN NVL(sal, 0)

7	7              ELSE 0

8	8          END) boston_salary

9	9  FROM   emp e

10

10  ;

11

12	NEW_YORK_SALARY BOSTON_SALARY

13	--------------- -------------

14	18150         10875

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

1	2         ,e.ename

2	3          ,e.job

3	4          ,CASE

4	5             WHEN job IN ('PRESIDENT', 'MANAGER') THEN -1

5	6              WHEN ( comm IS NOT NULL AND comm > 0) THEN 1

6	7              ELSE 0

7	8          END commission

8	9  FROM   emp e

9

10  ;

10

11	EMPNO ENAME      JOB       COMMISSION

12	----- ---------- --------- ----------

13	7369 SMITH      CLERK              0

14	7499 ALLEN      SALESMAN           1

15	7521 WARD       SALESMAN           1

16	7566 JONES      MANAGER           -1

17	7654 MARTIN     SALESMAN           1

18	7698 BLAKE      MANAGER           -1

19	7782 CLARK      MANAGER           -1

20	7788 SCOTT      ANALYST            0

21	7839 KING       PRESIDENT         -1

22	7844 TURNER     SALESMAN           0

23	7876 ADAMS      CLERK              0

24	7900 JAMES      CLERK              0

25	7902 FORD       ANALYST            0

26	7934 MILLER     CLERK              0

27

28	14 rows selected

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.

1	SQL> SELECT  d.deptno

2	2         ,d.dname

3	3          ,CASE

4	4             WHEN    EXISTS (

5	5                             SELECT  1

6	6                              FROM   emp e

7	7                              WHERE  e.deptno = d.deptno

8	8                              AND        e.comm IS NOT NULL

9	9                              AND        comm > 0

10	10                     ) THEN 1

11	11             ELSE 0

12	12         END received_commission

13	13  FROM   dept d

14

14  ;

15

16	DEPTNO DNAME          RECEIVED_COMMISSION

17	------ -------------- -------------------

18	10 ACCOUNTING                       0

19	20 RESEARCH                         0

20	30 SALES                            1

21	40 OPERATIONS                       0

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:

1	SQL> -- DECODE NULL

2	SQL> SELECT  DECODE(

3	2                 NULL -- expression

4	3                  ,NULL,'NULL' -- search and result # 1

5	4                  ,'NOT NULL' -- default value

6	5         ) null_value

7	6  FROM   dual

8

7  ;

9

10	NULL_VALUE

11	----------

12

NULL

13	SQL> -- Simple CASE expression MULL

14	SQL> SELECT  CASE NULL

15	2             WHEN NULL THEN 'NULL'

16	3             ELSE 'ELSE'

17	4         END null_value

18	5  FROM dual

19

6  ;

20

21	NULL_VALUE

22	----------

23

ELSE

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.