Oracle SQL_CASE Expressions

pexels-photo-935756 - Copy copy

admin DML Statements, Oracle , ,

Hello guys,
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 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.

1. Simple_case_expression:=
Syntax:

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.

2. Searched_case_expression:=
Syntax:

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.

Do you remember PRODUCT_INFORMATION table which we created while learning COALESCE Function? If you haven’t read yet, please click here and do read.

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.

If you like this blog, do share with your friends and colleagues on social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil

Sharing is caring!

You May Also Like..

DUAL TABLE

Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Concatenation Operator

Oracle_Concatenation Operator

Hey geeks, This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The […]

Parentheses

Parentheses in SQL

Hey time travellers, Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about […]

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *