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.

Practice

1. What does the searched CASE expression in SQL do?

easy

A. Deletes rows from a table

B. Joins two tables together

C. Creates a new table

D. Returns different results based on multiple conditions

Solution

1. Step 1: Understand the purpose of searched CASE

   The searched CASE expression evaluates multiple conditions and returns a result for the first true condition.

2. Step 2: Compare with other SQL operations

   Unlike joins, deletes, or table creation, CASE is used to return values conditionally within queries.

3. Final Answer:

   Returns different results based on multiple conditions -> Option D

4. Quick Check:

   Searched CASE = conditional results [OK]

Hint: Think of CASE as SQL's if-else for conditions [OK]

Common Mistakes:

• Confusing CASE with JOIN operations
• Thinking CASE modifies tables
• Assuming CASE deletes data

2. Which of the following is the correct syntax for a searched CASE expression in SQL?

easy

A. CASE WHEN condition THEN result ELSE default END

B. CASE condition WHEN THEN result ELSE END

C. CASE THEN condition WHEN result ELSE END

D. CASE WHEN THEN condition ELSE result END

Solution

1. Step 1: Recall the searched CASE syntax

   The correct syntax starts with CASE, followed by WHEN condition THEN result, optional ELSE, and ends with END.

2. Step 2: Check each option

   Only CASE WHEN condition THEN result ELSE default END follows the correct order: CASE WHEN condition THEN result ELSE default END.

3. Final Answer:

   CASE WHEN condition THEN result ELSE default END -> Option A

4. Quick Check:

   Syntax = CASE WHEN ... THEN ... ELSE ... END [OK]

Hint: Remember: WHEN condition THEN result, ELSE optional, END last [OK]

Common Mistakes:

• Mixing order of WHEN and THEN
• Omitting END keyword
• Placing ELSE before THEN

3. Given the table Employees with column Salary, what will this query return?

SELECT Salary, CASE WHEN Salary > 5000 THEN 'High' WHEN Salary > 3000 THEN 'Medium' ELSE 'Low' END AS SalaryLevel FROM Employees;

medium

A. Returns only salaries greater than 5000

B. Shows an error because ELSE is missing

C. Labels each salary as 'High', 'Medium', or 'Low' based on value

D. Labels all salaries as 'Low'

Solution

1. Step 1: Analyze the CASE conditions

   The CASE checks if Salary > 5000, then returns 'High'; else if Salary > 3000, returns 'Medium'; otherwise 'Low'.

2. Step 2: Understand the query output

   Each row shows Salary and its label based on these conditions, so salaries are categorized correctly.

3. Final Answer:

   Labels each salary as 'High', 'Medium', or 'Low' based on value -> Option C

4. Quick Check:

   CASE categorizes salaries correctly [OK]

Hint: Read CASE conditions top to bottom, first true wins [OK]

Common Mistakes:

• Thinking ELSE is required and missing causes error
• Assuming query filters rows instead of labeling
• Ignoring order of WHEN conditions

4. Identify the error in this searched CASE expression:

SELECT Name, CASE WHEN Age > 18 THEN 'Adult' WHEN Age < 18 THEN 'Minor' END AS AgeGroup FROM People;

medium

A. Incorrect use of THEN keyword

B. No error, query is correct

C. No END keyword to close CASE

D. Missing ELSE clause for other cases

Solution

1. Step 1: Check CASE syntax completeness

   The CASE has WHEN conditions, THEN results, and ends with END keyword properly.

2. Step 2: Consider ELSE clause necessity

   ELSE is optional; if no ELSE, NULL is returned for unmatched cases, which is valid.

3. Final Answer:

   No error, query is correct -> Option B

4. Quick Check:

   ELSE optional, END required, syntax correct [OK]

Hint: ELSE is optional; END is mandatory [OK]

Common Mistakes:

• Assuming ELSE is always required
• Forgetting END keyword
• Misplacing THEN keyword

5. You want to classify products by price range using searched CASE. Which query correctly assigns 'Cheap' for prices below 50, 'Affordable' for prices between 50 and 150, and 'Expensive' for prices above 150?

hard

A. SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

B. SELECT ProductName, CASE WHEN Price > 150 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

C. SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price > 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

D. SELECT ProductName, CASE WHEN Price BETWEEN 50 AND 150 THEN 'Cheap' WHEN Price < 50 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

Solution

1. Step 1: Match conditions to price ranges

   Price < 50 should be 'Cheap', 50 to 150 'Affordable', above 150 'Expensive'.

2. Step 2: Verify each option's logic

   SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products; correctly orders conditions and labels prices as required. Others mislabel or misorder conditions.

3. Final Answer:

   SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products; -> Option A

4. Quick Check:

   Conditions match price ranges correctly [OK]

Hint: Order CASE conditions from smallest to largest range [OK]

Common Mistakes:

• Mixing condition order causing wrong labels
• Using wrong comparison operators
• Misplacing BETWEEN ranges