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:=

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.

So let’s add another column COUNTRY using the CASE function in the SELECT query and see how it works.

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:=

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.

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.

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,

You May Also Like..

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 in SQL

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

Logical Conditions

Oracle – Logical Conditions

Hey time travellers, In this article, we will be discussing Logical Conditions. It is a very important aspect of SQL. […]

Leave a Reply

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