Hope you are doing well. I am back once again with a detailed blog on Oracle CASE function.
This is my favourite function whenever I need to evaluate multiple conditions. It is same as if-then-else PL/SQL block (we will discuss it in up-coming PL/SQL blog series) but we can use it in SQL statements.
When we use CASE function in any SELECT statement, it adds an additional column to the output and we can name that column whatever we want after the AS keyword. But don’t forget, this column is not added to the table definition. It is added just in the output for this particular SELECT statement.
In order to use, there are two types of CASE functions. We will discuss them one by one.
CASE expr WHEN comparison_expr THEN return_expr
ELSE else_expr END as “col_name”
In a simple CASE expression, Oracle Database searches for the first WHEN … THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN…THEN pairs meet this condition and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
In the above syntax, expr is a column of a table and comparison_expr, return_expr and else_expr can be column name of a table or any specific char/number value. Also, we can apply any functions as per the requirement.
For the demonstration, I have created a very simple table COUNTRY_NAME with only single column which is Country_id in Oracle database.
Let’s check that table first.
SELECT * FROM COUNTRY_NAME; Output… COUNTRY_ID ---------------- IN AU UK BR NL
So let’s add another column COUNTRY using the CASE function in the SELECT query and see how it works.
SELECT country_id, CASE country_id WHEN 'IN' THEN 'INDIA' WHEN 'AU' THEN 'AUSTRALIA' WHEN 'UK' THEN 'UNITED KINGDOM1' WHEN 'BR' THEN 'BRAZIL' ELSE 'UNKNOWN' END AS country FROM country_name; Output… COUNTRY_ID COUNTRY ---------- -------------- IN INDIA AU AUSTRALIA UK UNITED KINGDOM BR BRAZIL NL UNKNOWN
You can see in the output that we have a country column which has country names for all country_ids but not for NL. It is because in the CASE function, we have not provided comparison_expr as NL so CASE function added else_expr as UNKNOWN.
CASE WHEN condition THEN return_expr
ELSE else_expr END as “col_name”
In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null.
Have a look at the below code snippet. SQL statement is little different than the previous one but the Output is same.
SELECT country_id, CASE WHEN country_id = 'IN' THEN 'INDIA' WHEN country_id = 'AU' THEN 'AUSTRALIA' WHEN country_id = 'UK' THEN 'UNITED KINGDOM' WHEN country_id = 'BR' THEN 'BRAZIL' ELSE 'UNKNOWN' END AS country FROM country_name; Output… COUNTRY_ID COUNTRY ---------- -------------- IN INDIA AU AUSTRALIA UK UNITED KINGDOM BR BRAZIL NL UNKNOWN
Let’s do one more example for better understanding of Search case expression.
So today, we will try to achieve the same output using CASE function.
SELECT product_id, list_price, min_price, CASE WHEN list_price IS NOT NULL THEN ( 0.9 * list_price ) WHEN list_price IS NULL AND min_price IS NOT NULL THEN min_price ELSE 5 END AS "Sale" FROM product_information; Output… PRODUCT_ID LIST_PRICE MIN_PRICE Sale ---------- ---------- ---------- ---------- 1769 48 43.2 1770 73 73 2378 305 247 274.5 2382 850 731 765 3355 5
Please comment below and let me know if there are any differences in the out output.
If you have any questions please comment below or write me an email.