Summary: in this tutorial, you will learn how to use the Oracle CASE expression to add if-else logic to the SQL statements.
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.
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).
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.
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.
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)
Let’s take a few more examples of using the Oracle CASE expression to understand how it works.
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.
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.
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.
