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.
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)
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 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:
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)
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)
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)
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.
