Oracle CASE Expression

In Oracle Database, the CASE expression compares a list of conditions and returns one of multiple possible expressions. It allows us to use IF … THEN … ELSE logic in SQL statements without having to invoke procedures.

The CASE expression is included in the SQL standard (ISO/IEC 9075), and most major RDBMSs support it.

Oracle also has a CASE statement that’s very similar to the CASE expression, but with some minor differences.

Syntax

The syntax goes like this:

CASE { simple_case_expression
     | searched_case_expression
     }
     [ else_clause ]
     END

That indicates that the CASE expression can take two forms; the simple CASE expression, and the the searched CASE expression.

Here’s the syntax for simple_case_expression:

expr
  { WHEN comparison_expr THEN return_expr }...

When using this form, the expr part is compared to a set of simple expressions to determine the result.

And here’s the syntax for searched_case_expression:

{ WHEN condition THEN return_expr }...

The searched CASE expression evaluates a set of expressions to determine the result. No base expression is supplied.

The optional ELSE clause goes like this:

ELSE else_expr

The ELSE clause determines what’s returned if none of the conditions are met.

Example

Here’s an example to demonstrate the simple CASE expression:

SELECT
    OE#,
    Model,
    CASE Model
        WHEN 'HILUX' THEN 'Toyota'
        WHEN 'COMMODORE' THEN 'Holden'
        WHEN 'X-TRAIL' THEN 'Nissan'
        ELSE 'Unknown'
    END AS Manufacturer
FROM Autoparts
ORDER BY Manufacturer;

Result:

OE#	MODEL	MANUFACTURER
62150C3278	COMMODORE	Holden
86000-7900	X-TRAIL	Nissan
62150A3278	HILUX	Toyota
86001-7800	HILUX	Toyota
62150B3278	CAMRY	Unknown

We could rewrite this to a searched CASE expression. Doing this means we would remove the base expression, but then include it in the WHEN part of the statement:

SELECT
    OE#,
    Model,
    CASE
        WHEN Model = 'HILUX' THEN 'Toyota'
        WHEN Model = 'COMMODORE' THEN 'Holden'
        WHEN Model = 'X-TRAIL' THEN 'Nissan'
        ELSE 'Unknown'
    END AS Manufacturer
FROM Autoparts
ORDER BY Manufacturer;

Result:

OE#	MODEL	MANUFACTURER
62150C3278	COMMODORE	Holden
86000-7900	X-TRAIL	Nissan
62150A3278	HILUX	Toyota
86001-7800	HILUX	Toyota
62150B3278	CAMRY	Unknown

Same result.

The Optional ELSE Clause

The above examples use the ELSE clause to determine what happens if none of the conditions are met, however, the ELSE clause is optional.

If we omit the ELSE clause, and none of the conditions are met, the result is NULL:

SELECT
    OE#,
    Model,
    CASE Model
        WHEN 'HILUX' THEN 'Toyota'
        WHEN 'COMMODORE' THEN 'Holden'
        WHEN 'X-TRAIL' THEN 'Nissan'
    END AS Manufacturer
FROM Autoparts
ORDER BY Manufacturer;

Result:

OE#	MODEL	MANUFACTURER
62150C3278	COMMODORE	Holden
86000-7900	X-TRAIL	Nissan
62150A3278	HILUX	Toyota
86001-7800	HILUX	Toyota
62150B3278	CAMRY	–

This time, instead of getting Unknown in the last row, we got -, which indicates that it’s a NULL value.

Using the CASE Expression in Other Contexts

The CASE expression isn’t limited to just SELECT statements. We can use it pretty much anywhere a valid expression can be used.

For example, we can use the CASE expression as part of an UPDATE statement when updating data in a database. We can also use it in an INSERT statement, and even in an ORDER BY clause. See SQL CASE Statement for examples.