Searched CASE syntax in SQL - Step-by-Step Execution

Concept Flow - Searched CASE syntax

Start

↓

Evaluate WHEN condition 1

↓

Return THEN result 1

→End

No↓

Evaluate WHEN condition 2

↓

Return THEN result 2

→End

No↓

Evaluate more WHEN conditions or ELSE

↓

Return ELSE result if no WHEN true

↓

End

The searched CASE checks each WHEN condition in order and returns the THEN result for the first true condition, or ELSE if none match.

Execution Sample

SQL

SELECT product_name,
  CASE
    WHEN price < 20 THEN 'Cheap'
    WHEN price BETWEEN 20 AND 50 THEN 'Moderate'
    ELSE 'Expensive'
  END AS price_category
FROM products;

This query classifies products into price categories based on their price using searched CASE.

Execution Table

Step	product_name	price	WHEN condition evaluated	Condition result	THEN/ELSE returned	Output row
1	Pen	10	price < 20	True	'Cheap'	Pen, Cheap
2	Notebook	30	price < 20	False	Check next WHEN	Notebook, Moderate
3	Notebook	30	price BETWEEN 20 AND 50	True	'Moderate'	Notebook, Moderate
4	Backpack	70	price < 20	False	Check next WHEN	Backpack, Expensive
5	Backpack	70	price BETWEEN 20 AND 50	False	Check next WHEN	Backpack, Expensive
6	Backpack	70	ELSE	N/A	'Expensive'	Backpack, Expensive
7	END					Query complete

💡 All rows processed, CASE returned first matching THEN or ELSE result.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
product_name	N/A	Pen	Notebook	Backpack	N/A
price	N/A	10	30	70	N/A
price_category	N/A	Cheap	Moderate	Expensive	N/A

Key Moments - 2 Insights

Why does the CASE stop checking conditions after the first true WHEN?

Because searched CASE returns the THEN result of the first WHEN condition that is true and skips the rest, as shown in rows 1 and 3 of the execution_table.

What happens if none of the WHEN conditions are true?

The ELSE clause is used to return a default value, as shown in rows 4-6 for the Backpack product in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what price_category is assigned to the product 'Notebook' at step 3?

A 'Expensive'

B 'Cheap'

C 'Moderate'

D No category assigned

💡 Hint

Check the 'THEN/ELSE returned' column at step 3 in the execution_table.

At which step does the CASE return the ELSE result?

A Step 2

B Step 6

C Step 3

D Step 1

💡 Hint

Look for the row where 'ELSE' is evaluated in the 'WHEN condition evaluated' column.

If the price of 'Backpack' was 40, what would be the price_category returned?

A 'Moderate'

B 'Cheap'

C 'Expensive'

D No category assigned

💡 Hint

Refer to the condition 'price BETWEEN 20 AND 50' and how it applies in the execution_table.

Concept Snapshot

Searched CASE syntax:
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END
- Checks conditions in order
- Returns first THEN where condition is true
- ELSE is optional fallback
- Used for conditional logic in SELECT

Full Transcript

The searched CASE syntax in SQL evaluates multiple conditions in order. For each row, it checks the first WHEN condition; if true, it returns the corresponding THEN result and stops checking further. If none of the WHEN conditions are true, it returns the ELSE result if provided. This allows classification or conditional output in queries. The example query classifies products by price into 'Cheap', 'Moderate', or 'Expensive'. The execution table shows step-by-step how each product's price is checked against conditions and the category assigned. Key points include that CASE stops at the first true condition and uses ELSE as a fallback. The visual quiz tests understanding of these steps and outcomes.