Oracle CASE Expression

Summary: in this tutorial, you will learn how to use the Oracle CASE expression to add if-else logic to the SQL statements.

Introduction to Oracle CASE expression

Oracle CASE expression allows you to add if-else logic to SQL statements without having to call a procedure. The CASE expression evaluates a list of conditions and returns one of the multiple possible results.

You can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT, UPDATE, or DELETE, and in clauses like SELECT, WHERE, HAVING, and ORDDER BY.

Oracle CASE expression has two formats: the simple CASE expression and the searched CASE expression. Both formats support an optional ELSE clause.

Simple CASE expression

The simple CASE expression matches an expression to a list of simple expressions to determine the result.

The following illustrates the syntax of the simple CASE expression:

CASE e
    WHEN e1 THEN
          r1
    WHEN e2 THEN
          r2
    WHEN en THEN
          rn 
    [ ELSE r_else ]
END
Code language: SQL (Structured Query Language) (sql)

In this syntax, Oracle compares the input expression (e) to each comparison expression e1, e2, …, en.

If the input expression equals any comparison expression, the CASE expression returns the corresponding result expression (r).

If the input expression e does not match any comparison expression, the CASE expression returns the expression in the ELSE clause if the ELSE clause exists, otherwise, it returns a null value.

Oracle uses short-circuit evaluation for the simple CASE expression. It means that Oracle evaluates each comparison expression (e1, e2, .. en) only before comparing one of them with the input expression (e). Oracle does not evaluate all comparison expressions before comparing any of them with the expression (e). As a result, Oracle never evaluates a comparison expression if a previous one equals the input expression (e).

Simple CASE expression example

We will use the products table in the sample database for the demonstration.

products table

The following query uses the CASE expression to calculate the discount for each product category i.e., CPU 5%, video card 10%, and other product categories 8%

SELECT
  product_name,
  list_price,
  CASE category_id
    WHEN 1
    THEN ROUND(list_price * 0.05,2) -- CPU
    WHEN 2
    THEN ROUND(List_price * 0.1,2)  -- Video Card
    ELSE ROUND(list_price * 0.08,2) -- other categories
  END discount
FROM
  products
ORDER BY
  product_name;
Code language: SQL (Structured Query Language) (sql)
Oracle CASE - SELECT example

Note that we used the ROUND() function to round the discount to two decimal places.

Searched CASE expression

The Oracle searched CASE expression evaluates a list of Boolean expressions to determine the result.

The searched CASE statement has the following syntax:

CASE 
    WHEN e1 THEN r1
    [ WHEN e2 THEN r2]
    ...
    [ELSE
        r_else]
END
Code language: SQL (Structured Query Language) (sql)

The searched CASE expression evaluates the Boolean expression (e1, e2, …) in each WHEN clause in the order that the Boolean expressions appear. It returns the result expression (r) of the first Boolean expression (e) that evaluates to true.

If no Boolean expression is true, then the CASE expression returns the result expression in the ELSE clause if an ELSE clause exists; otherwise, it returns a null value.

Like the simple CASE expression, Oracle also uses short-circuit evaluation for the searched CASE expression. In other words, Oracle evaluates each Boolean condition to determine whether it is true, and never evaluates the next condition if the previous one is true.

Searched CASE expression example

When you use the searched CASE expression within a SELECT statement, you can replace values in the result based on comparison values.

The following example uses the searched CASE expression to classify the products based on their list prices:

SELECT
  product_name,
  list_price,
  CASE
    WHEN list_price > 0 AND list_price  < 600
        THEN 'Mass'
    WHEN list_price >= 600 AND list_price < 1000
        THEN 'Economy'
    WHEN list_price >= 1000 AND list_price < 2000
        THEN 'Luxury'
    ELSE 
        'Grand Luxury'
  END product_group
FROM
  products
WHERE
  category_id = 1
ORDER BY
  product_name;
Code language: SQL (Structured Query Language) (sql)
Oracle Searched CASE example

Oracle CASE expression examples

Let’s take a few more examples of using the Oracle CASE expression to understand how it works.

A) Using CASE expression in an ORDER BY clause

See the following locations table:

locations table

The following query uses the CASE expression in an ORDER BY clause to determine the sort order of rows based on column value:

SELECT
  *
FROM
  locations
WHERE country_id in ('US','CA','UK')
ORDER BY
   country_id,
  CASE country_id
    WHEN 'US'
    THEN state
    ELSE city
  END;
Code language: SQL (Structured Query Language) (sql)
Oracle CASE - ORDER BY example

In this example, the result set is sorted by the column state when the country is the US and by the column city for all other countries.

B) Using CASE expression in a HAVING clause

Consider the following order_items and products tables:

In the following query, we use the CASE expression in a HAVING clause to restrict rows returned by the SELECT statement.

SELECT 
   product_name, 
   category_id,
   COUNT(product_id)
FROM 
   order_items 
INNER JOIN products USING (product_id)
GROUP BY 
    product_name, 
    category_id
HAVING 
    COUNT(CASE WHEN category_id = 1 THEN product_id ELSE NULL END ) > 5 OR
    COUNT(CASE WHEN category_id = 2 THEN product_id ELSE NULL END) > 2
ORDER BY 
   product_name;
Code language: SQL (Structured Query Language) (sql)
Oracle CASE - HAVING clause example

As shown in the output, the statement returned the number of products for each product that appears in the order_items table. The HAVING clause restricted the products to those that were CPU (category id 1) with the number of products greater than 5 and Video Card (category id 2) with the number of products greater than 2.

C) Using the CASE expression in an UPDATE statement

The following query finds all products whose gross margins are less than 12%:

SELECT
  product_name,
  list_price,
  standard_cost,
  ROUND((list_price - standard_cost) * 100 / list_price,2) gross_margin
FROM
  products
WHERE
  ROUND((list_price - standard_cost) * 100 / list_price,2) < 12;
Code language: SQL (Structured Query Language) (sql)
Oracle CASE - UPDATE example

The following UPDATE statement uses the CASE expression to update the list price of the products whose gross margin is less than 12% to list prices that make their gross margin 12%:

UPDATE
  products
SET
  list_price =
  CASE
    WHEN ROUND((list_price - standard_cost) * 100 / list_price,2) < 12
    THEN (standard_cost  + 1) * 12
  END
WHERE
  ROUND((list_price - standard_cost) * 100 / list_price,2) < 12;
Code language: SQL (Structured Query Language) (sql)

Now, you should know how to use the Oracle CASE expression including simple and searched CASE expressions to add if-else logic to SQL statements.

Was this tutorial helpful?