Bugl — Daily coding puzzles and drills

The SQL CASE Expression

The CASE

expression is used to define different results based on specified conditions in an SQL statement.

The CASE

expression goes through the conditions and stops at the first match (like an if-then-else statement). So, once a condition is true, it will stop processing and return the result. If no conditions are true, it returns the value in the ELSE

clause. If there is no

Else

clause and no conditions are true, it returns NULL.

CASE Syntax

Case When

condition1

Then

result1

When

condition2

Then

result2

When

conditionN

Then

resultN

Else

default_result

END;

SQL CASE Example

Here we use the CASE

expression to categorize data (Price) and we create a new column (PriceCategory) that shows in which price category each product is:

Example

SELECT ProductName, Price,

Case

Formula

WHEN Price < 20 THEN 'Low Cost'
WHEN Price BETWEEN 20 AND 50 THEN 'Medium Cost'

ELSE 'High Cost'

END AS PriceCategory

FROM Products;