Oracle CASE Expression

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

Introduction to Oracle CASE expression #

Oracle CASE expression allows you to add if-else logic to SQL statements without calling 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 ORDER BY.

The CASE expression has two forms:

Simple CASE expression.
Searched CASE expression.

Both forms 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.

Here’s the syntax of the simple CASE expression:

CASE e
    WHEN e1 THEN
          r1
    WHEN e2 THEN
          r2
    WHEN en THEN
          rn 
    [ ELSE r_else ]
ENDCode 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 NULL.

The CASE expression uses short-circuit evaluation. This means the CASE expression evaluates each comparison expression (e1, e2, .. en) one at a time, and compares it immediately with the input expression (e).

Oracle does not evaluate all comparison expressions up front. Instead, it stops evaluating as soon as it finds a match. So, if a comparison expression equals e, the CASE expression skips evaluating the remaining expressions.

Simple CASE expression example #

We’ll use the products table in the sample database for the demonstration.

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)

Try it

Output:

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.

Here’s the basic syntax of the searched CASE statement:

CASE 
    WHEN e1 THEN r1
    [ WHEN e2 THEN r2]
    ...
    [ELSE
        r_else]
ENDCode 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 NULL.

Like the simple CASE expression, the searched case expression also uses short-circuit evaluation.

Searched CASE expression example #

When using 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)

Try it

Oracle CASE expression examples #

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

Using CASE expression in an ORDER BY clause #

See the following 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)

Try it

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.

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)

Try it

The output shows that 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.

Using CASE in an expression #

The following example uses the CASE in an expression to set the new price for the products in the category id 1 and 2:

SELECT
  product_name,
  category_id,
  list_price,
  list_price + CASE category_id
    WHEN 1 THEN round(list_price * 0.05, 2)
    WHEN 2 THEN round(list_price * 0.1, 2)
    ELSE 0
  END AS new_price
FROM
  products
WHERE
  category_id IN (1, 2)
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

How it works.

Add 5% to the list price of the products in category ID 1 and 10% to the list price in category ID 2.
Add the price increments to the list prices using the + operator.

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)

Try it

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
    ELSE list_price
  END;Code language: SQL (Structured Query Language) (sql)

Summary #

Use the Oracle CASE expression including simple and searched CASE expressions to add if-else logic to SQL statements.

Quiz #

Was this tutorial helpful?