IF-THEN logic in SELECT and WHERE with CASE expressions in Oracle SQL

There’s no if keyword in SQL. If you want to do if-else-then logic in select, where or anywhere else in a statement, you need a case expression.

This is a series of when clauses that the database runs in order:

For example, if you want to map exam correct percentages to grade letters according to these rules:

• 90% or greater => A
• 80% or greater but less than 90% => B
• 70% or greater but less than 80% => C
• 60% or greater but less than 70% => D
• 50% or greater but less than 60% => E
• Less than 50% => F

You can use a case expression like this:

The database processes the expression from top-to-bottom. It returns the value for the first when clause that is true. If none are true (the percentage is less than 50 or null), it returns the value in the else clause which is F.

For example, if a student scored 75% correct on an exam the database runs these operations:

• >= 90 => false
• >= 80 => false
• >= 70 => true => return C

The order of clauses is important! If you reverse them in the case like this:

If a student scores 75%, then the database processes:

• < 50 => false
• >= 50 => true => return E

Thus everyone scoring 50% or higher will get grade E!

Ensuring the when clauses are in the correct order is one of many things to be aware of when writing case expressions. In this post we’ll dive into:

• Simple case expressions
• Searched case expressions
• How to use case in select
• How to use case in where
• How to use case in joins
• Case in PL/SQL

If you’d like to see a demo, check the recording of the Ask TOM Office Hours on case expressions. Finally get the scripts and the case in SQL cheat sheet.

Map key-value pairs with simple CASE expressions

With a simple case you can check if an input equals any listed key. If it does you can return a corresponding value.

This structure of this is:

This

• Evaluates <selector> once
• Works down the list of when clauses, stopping on the first key that equals the <selector> and returning the corresponding value
• If the <selector> matches none of the clauses, if you have an else clause it returns <else_value>. Otherwise it returns <null>.

For example, to map exam ids to names according to these rules:

• 1 => SQL
• 2 => Java
• 3 => Python
• 4 => JavaScript

You can use a simple expression like :

This has no else clause, so returns null for values of exam_id other than 1, 2, 3, or 4.

CASE vs DECODE

A simple case expression is like decode. This also has one selector followed by a list of key-value pairs. Instead of the expression above, you could use this decode logic:

Choosing between case and decode in these situations is down to personal preference. They offer the same functionality and performance. Personally I prefer case expressions, because I find it easier to understand. They’re also easier to adapt if you need to change the expression to have range or other non-equality comparisons.

To do this you need a searched case expression.

Map complex conditions to values with searched CASE expressions

The format for a searched case expression is the syntax shown above:

The core differences between this and simple expressions are:

• You can use multiple selectors
• The selectors are evaluated in every when clause the database runs
• Any SQL conditions that return true or false are possible in the <boolean conditions>

The exam percentage to grade letter at the start of this posted used a searched expression.

Let’s expand its requirements. The application uses the id zero for test entities. The expression must return TEST if the result is for either the test exam or test student.

To do this, you need update the case to check these values.

There are several ways you can do this. One is to add the student and exam ids checks in separate when clauses at the top:

Or you could have one clause that checks both the exam and student ids. Then place the original expression in the else clause:

Nesting cases like this can make sense if you’ve made a function for the percentages to letter conversions. For example:

How you decide to structure complex case expressions is a combination of:

• Clarity – some ways will be easier to understand later
• Performance – the database stops processing case when it finds the first true condition. Ordering the clauses so the most likely are at the top can make the expression faster. In most cases, this benefit is tiny so only worth considering when speed is critical.

With your case expression in hand, using it is a matter of pasting it in the appropriate place in your SQL.

How to use CASE for IF-THEN logic in SQL SELECT

If you want to see the grade for each exam, select the case expression like a regular column:

It’s a good idea to give the expression an alias. This is particularly important if the case is in a subquery.

How to use CASE in the WHERE clause

If you want to find all the exam results with A or B grades, you can place the select above in a subquery. Then filter it in the outer query.

Or you can slap the case expression directly in the where clause, like so:

Using columns in case expressions like this limits the optimizer’s ability to use indexes on those columns. To overcome this, you can index the expression itself:

How to join to a CASE expression

You can also join tables using case. For example, you may have a table of exam grades you want to link to the exam results.

Use the case expression in the join clause like so:

As with where, joining to case limits the optimizer. If you find yourself using case in many joins, you may want to rethink your data model!

Make reusable CASE expressions with virtual columns

Writing case expressions directly in SQL statements is fine for one-off logic. Often you’ll want to reuse this logic in many queries. Copy-pasting it into every statement is error-prone and hard to maintain.

You can avoid these problems by moving the logic into the table definition with virtual columns.

This statement adds a column mapping percentages to grades:

With this in place, you then access the expression like a regular column:

If you need to use this logic in many tables you could place it in a PL/SQL function. Then call this function in your SQL:

From Oracle AI Database 26ai, the automatic SQL transpiler can extract SQL expressions in PL/SQL. These are then part of the SQL statement, so at runtime it’s as-if the function doesn’t exist!

To do this, ensure the sql_transpiler parameter is on (it’s off by default). When a function in the where clause is transpiled, you can see the case expression instead of the function in the predicate section of the plan:

There are a few differences between case in PL/SQL and Oracle SQL.

Using case in PL/SQL

There are several enhancements to case available in PL/SQL:

• case statements
• Extended case controls (from Oracle AI Database 26ai)

Case statements in PL/SQL

A case expression returns a single value. In PL/SQL you can write a case statement to run one or more actions.

The differences between case expressions and statements are:

• You complete them with end case (instead of just end)
• Each then/else clause contains a statement, rather than returning a value

For example, you could write a statement that assigns grade letters and pass/fail outcomes to variables:

Functionally these are similar to a series if-elsif statements in PL/SQL.

So you may be wondering why bother with case statements?

Case statements can raise a case_not_found exception. This happens if they run without processing one of the clauses. i.e. none of the when conditions are true and there’s no else clause.

There is no such exception for if-elsif blocks. If you have a series like this:

The database will continue processing if none are true.

Thus case statements are useful when you want to ensure that a block of code always runs an action.

Extended case controls

The original logic to map exam results to grades checked the percent_correct in every clause. Duplicating it in every position means the database evaluates it for every when clause run.

If it’s a regular table column this is no big deal. But what if its calls a slow function?

It would be better to list and check it once, like a simple case expression.

From Oracle AI Database 26ai you can get the “best of both” with extended case controls.

These enable you to:

• List and evaluate the selector once
• Compare it using all SQL conditions

For the comparison, the selector is implicitly on the left-hand side. You just list the condition and the values to compare it to.

For example, let’s extend the exam result to grade logic to also return:

• A* if the result is 100%
• U if the percentage is null or zero

You can use a case expression like this:

This enhancement is currently only in PL/SQL.

See CASE statements in action

I walked through the details of case statements in August 2023’s SQL Office Hours. Watch the recording here:

Try them out yourself with these Live SQL scripts. Use this script to create the tables shown in this post:

CASE expressions cheat sheet

Here’s an overview of the different types of case in Oracle Database:

UPDATED 16 Apr 2025: Changed Live SQL links to new platform

UPDATED 20 Oct 2025: Oracle AI Database 26ai replaces Oracle Database 23ai