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…
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.
According to the Oracle Database SQL Language Reference, the DECODE syntax is as follows:
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:
DECODE ( expression ,search1, result1 ,search2, result2 ,search3, result3 , default )
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
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:
SQL> SELECT e.empno 2 ,e.ename 3 ,deptno 4 FROM emp e 5 ; EMPNO ENAME DEPTNO ----- ---------- ------ 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 14 rows selected
If we want to show the department in our query, we can use the DECODE statement like this:
SQL> SELECT e.empno 2 ,e.ename 3 ,deptno 4 ,DECODE( 5 deptno 6 ,10, 'ACCOUNTING' 7 ,20, 'RESEARCH' 8 ,30, 'SALES' 9 ,40, 'OPERATIONS' 10 ) deptname 11 FROM emp e 12 ; EMPNO ENAME DEPTNO DEPTNAME ----- ---------- ------ ---------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 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:
SQL> SELECT e.empno 2 ,e.ename 3 ,deptno 4 ,DECODE( 5 deptno 6 ,10, 'ACCOUNTING' 7 ,20, 'RESEARCH' 8 ,30, 'SALES' 9 ,40, 'OPERATIONS' 10 ) deptname 11 ,DECODE( 12 deptno 13 ,10, 'New York' 14 ,20, 'Boston' 15 ,30, 'New York' 16 ,40, 'Boston' 17 ) city 18 FROM emp e 19 ; EMPNO ENAME DEPTNO DEPTNAME CITY ----- ---------- ------ ---------- -------- 7369 SMITH 20 RESEARCH Boston 7499 ALLEN 30 SALES New York 7521 WARD 30 SALES New York 7566 JONES 20 RESEARCH Boston 7654 MARTIN 30 SALES New York 7698 BLAKE 30 SALES New York 7782 CLARK 10 ACCOUNTING New York 7788 SCOTT 20 RESEARCH Boston 7839 KING 10 ACCOUNTING New York 7844 TURNER 30 SALES New York 7876 ADAMS 20 RESEARCH Boston 7900 JAMES 30 SALES New York 7902 FORD 20 RESEARCH Boston 7934 MILLER 10 ACCOUNTING New York 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
CASE expression WHEN comparison_expression_1 THEN return_expression_1 WHEN comparison_expression_2 THEN return_expression_2 .. --AND so ON .. ELSE DEFAULT 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
CASE WHEN condition1 THEN return_expression1 WHEN condition2 THEN return_expression2 .. --and so on .. ELSE default 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
SQL> SELECT e.empno 2 ,e.ename 3 ,deptno 4 ,CASE deptno 5 WHEN 10 THEN 'ACCOUNTING' 6 WHEN 20 THEN 'RESEARCH' 7 WHEN 30 THEN 'SALES' 8 WHEN 40 THEN 'OPERATIONS' 9 END deptname 10 FROM emp e 11 ; EMPNO ENAME DEPTNO DEPTNAME ----- ---------- ------ ---------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING 14 rows selected
As we can see, the same result as with DECODE.
CASE sample # 2: Searched CASE expression with range
SQL> SELECT e.empno 2 ,e.ename 3 ,deptno 4 ,CASE deptno 5 WHEN 10 THEN 'ACCOUNTING' 6 WHEN 20 THEN 'RESEARCH' 7 WHEN 30 THEN 'SALES' 8 WHEN 40 THEN 'OPERATIONS' 9 END deptname 10 ,CASE 11 WHEN deptno IN (10, 30) THEN 'New York' 12 WHEN deptno IN (20, 40) THEN 'Boston' 13 END city 14 FROM emp e 15 ; EMPNO ENAME DEPTNO DEPTNAME CITY ----- ---------- ------ ---------- -------- 7369 SMITH 20 RESEARCH Boston 7499 ALLEN 30 SALES New York 7521 WARD 30 SALES New York 7566 JONES 20 RESEARCH Boston 7654 MARTIN 30 SALES New York 7698 BLAKE 30 SALES New York 7782 CLARK 10 ACCOUNTING New York 7788 SCOTT 20 RESEARCH Boston 7839 KING 10 ACCOUNTING New York 7844 TURNER 30 SALES New York 7876 ADAMS 20 RESEARCH Boston 7900 JAMES 30 SALES New York 7902 FORD 20 RESEARCH Boston 7934 MILLER 10 ACCOUNTING New York 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:
SQL> SELECT e.empno 2 ,e.ename 3 ,deptno 4 ,CASE 5 WHEN deptno IN (10, 30) THEN 'New York' 6 WHEN deptno IN (20, 40) THEN 'Boston' 7 END city 8 ,CASE 9 WHEN deptno IN (10, 30) THEN NVL(sal, 0) 10 ELSE 0 11 END new_york_salary 12 ,CASE 13 WHEN deptno IN (20, 40) THEN NVL(sal, 0) 14 ELSE 0 15 END boston_salary 16 FROM emp e 17 ; EMPNO ENAME DEPTNO CITY NEW_YORK_SALARY BOSTON_SALARY ----- ---------- ------ -------- --------------- ------------- 7369 SMITH 20 Boston 0 800 7499 ALLEN 30 New York 1600 0 7521 WARD 30 New York 1250 0 7566 JONES 20 Boston 0 2975 7654 MARTIN 30 New York 1250 0 7698 BLAKE 30 New York 2850 0 7782 CLARK 10 New York 2450 0 7788 SCOTT 20 Boston 0 3000 7839 KING 10 New York 5000 0 7844 TURNER 30 New York 1500 0 7876 ADAMS 20 Boston 0 1100 7900 JAMES 30 New York 950 0 7902 FORD 20 Boston 0 3000 7934 MILLER 10 New York 1300 0 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:
SQL> SELECT SUM(CASE 2 WHEN deptno IN (10, 30) THEN NVL(sal, 0) 3 ELSE 0 4 END) new_york_salary 5 ,SUM(CASE 6 WHEN deptno IN (20, 40) THEN NVL(sal, 0) 7 ELSE 0 8 END) boston_salary 9 FROM emp e 10 ; NEW_YORK_SALARY BOSTON_SALARY --------------- ------------- 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
2 ,e.ename 3 ,e.job 4 ,CASE 5 WHEN job IN ('PRESIDENT', 'MANAGER') THEN -1 6 WHEN ( comm IS NOT NULL AND comm > 0) THEN 1 7 ELSE 0 8 END commission 9 FROM emp e 10 ; EMPNO ENAME JOB COMMISSION ----- ---------- --------- ---------- 7369 SMITH CLERK 0 7499 ALLEN SALESMAN 1 7521 WARD SALESMAN 1 7566 JONES MANAGER -1 7654 MARTIN SALESMAN 1 7698 BLAKE MANAGER -1 7782 CLARK MANAGER -1 7788 SCOTT ANALYST 0 7839 KING PRESIDENT -1 7844 TURNER SALESMAN 0 7876 ADAMS CLERK 0 7900 JAMES CLERK 0 7902 FORD ANALYST 0 7934 MILLER CLERK 0 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.
SQL> SELECT d.deptno 2 ,d.dname 3 ,CASE 4 WHEN EXISTS ( 5 SELECT 1 6 FROM emp e 7 WHERE e.deptno = d.deptno 8 AND e.comm IS NOT NULL 9 AND comm > 0 10 ) THEN 1 11 ELSE 0 12 END received_commission 13 FROM dept d 14 ; DEPTNO DNAME RECEIVED_COMMISSION ------ -------------- ------------------- 10 ACCOUNTING 0 20 RESEARCH 0 30 SALES 1 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:
SQL> -- DECODE NULL SQL> SELECT DECODE( 2 NULL -- expression 3 ,NULL,'NULL' -- search and result # 1 4 ,'NOT NULL' -- default value 5 ) null_value 6 FROM dual 7 ; NULL_VALUE ---------- NULL SQL> -- Simple CASE expression MULL SQL> SELECT CASE NULL 2 WHEN NULL THEN 'NULL' 3 ELSE 'ELSE' 4 END null_value 5 FROM dual 6 ; NULL_VALUE ---------- 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.
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.
If you have any other differences, or similarities that should have been mentioned in this article, please share with us all using the comments below.
~ TJ Abrahamsen