CASE expressions in Oracle SQL

CASE expressions are the standard way to implement IF … THEN … ELSE logic in SQL statements. Oracle provides the DECODE function, which does something similar and is widely used by Oracle developers, but CASE expressions are actually more flexible and often make statements more readable.

There are 2 types of CASE expressions: Simple and Searched

Simple CASE Expressions

Simple CASE expressions are those in which you provide an expression that must be compared with values or expressions provided for each case. For those who have worked with Java or C, it works almost exactly like the SWITCH statement in those languages.

Here is an example:

SELECT country_name,
  CASE region_id
    WHEN 1
    THEN 'Europe'
    WHEN 2
    THEN 'Americas'
    WHEN 3
    THEN 'Asia'
    WHEN 4
    THEN 'Middle East and Africa'
    ELSE 'Undefined'
  END Region
FROM hr.countries;

In this query region_id is the expression that is going to be compared with all the cases provided. If region_id is equal to 1 then the whole CASE expression returns ‘Europe’. If region_id is equal to 2, then it returns ‘Americas’, etc. If none of the values provided is equal to region_id, then the expression in the ‘ELSE’ case is returned, in this case, ‘Undefined’.

Notice that no comparison operator was provided, and that is because in simple CASE expressions the comparison is always performed using the equality operator.

In Oracle the system starts evaluating the cases in order, and it stops when a match is found.

Searched CASE Expressions

Now, in searched CASE expressions, instead of providing an expression to be compared to the other expressions provided, you can have more complex and independent conditions, which makes CASE expressions a lot more flexible than the DECODE function.

In the article about the power of the DECODE function I used this example:
Suppose we have a list of products that can or cannot be sold depending on certain conditions. A product is saleable if it has been verified (column verified=’Y’) or if it is not expired and is in stock. How do we get a list of products with a column that tells us if it can be sold?

It can be solved by nesting 3 calls to the DECODE function, and thus it can also be solved by nesting simple CASE expressions, but using a searched CASE expression makes it more easy to read and understand:

Instead of this:

SELECT
  product_id,
  product_name,
  DECODE(verified,'Y','YES',                  -- If verified=Y it is saleable
    DECODE(SIGN(sysdate-expiration_date),1,   -- If not verified but not expired, we need to check stock
      DECODE(in_stock,'Y','YES','NO'),'NO')) AS saleable  -- If not expired and in stock, then is saleable otherwise no
FROM PRODUCTS
ORDER BY product_id;

I can do something like this:

SELECT product_id, product_name,
  CASE
    WHEN verified = 'Y'
      OR (expiration_date > sysdate
          AND in_stock = 'Y') 
    THEN 'YES'
    ELSE 'NO'
  END AS saleable
FROM PRODUCTS
ORDER BY product_id;

As you can see, I’m comparing very different things in the conditions, and I can have several sub-conditions connected by ANDs and ORs, and I don’t need to nest functions. I’m sure you would agree that understanding the logic for a product to be saleable is a lot easier by looking at this CASE expression than with the DECODE function.

I only have one case here, but I could have any number of cases with completely different and unrelated conditions, and, in case you are wondering, yes, you can also nest CASE expressions.

And as with simple CASE expressions, cases are evaluated in order here too, and the evaluation ends when the first case condition evaluates to true.

What else can I do with them?

You can also use CASE expressions with aggregate functions, as in this example in which I’m including the bonus column in the sum when the bonus_active column is set to ‘Y’:

SELECT department_id, SUM(
    CASE bonus_active
      WHEN 'Y'
      THEN salary + bonus
      ELSE salary
    END) earns
FROM employees
GROUP BY department_id;

And there are some cool things you can do with CASE expressions outside the SELECT list.

For example, you can use it in the HAVING clause:

SELECT region_id, COUNT( * )
FROM my_countries
GROUP BY region_id
HAVING
  (
    CASE region_id
      WHEN 1
      THEN COUNT( * )
      ELSE COUNT( nullable_column )
    END
  ) > 5;

And you can even use it to order the results conditionally:

SELECT country_name, region_id,
  CASE region_id
    WHEN 1
    THEN 'Europe'
    WHEN 2
    THEN 'Americas'
    WHEN 2
    THEN 'Asia'
    WHEN 4
    THEN 'Middle East and Africa'
    ELSE 'Undefined'
  END Region
FROM hr.countries
ORDER BY
  CASE region_id
    WHEN 1
    THEN SUBSTR(country_name, 1, 1)
    ELSE SUBSTR(country_name, 2, 1)
  END;

Here if region_id is 1 I’m ordering by the first letter of the country name, otherwise I order by the second letter. Pretty cool, isn’t?

In what other uncommon ways have you used or seen CASE expressions?

Don't want to miss any Oracle SQL tips?

Subscribe to be informed about new posts, tips and more awesome things.

As a welcome gift, I will send you a copy of my Basic SQL Developer Cheat Sheet for Developers - A small compilation of my favorite SQL Developer features and shortcuts.