Why CASE expressions are needed in SQL - Visual Breakdown

Concept Flow - Why CASE expressions are needed

Start Query

↓

Evaluate CASE expression

↓

Check WHEN conditions one by one

↓

WHEN condition true?

Yes→Return THEN result

↓

Check next WHEN

↓

No WHEN true?

→Return ELSE result or NULL

↓

Continue with rest of query

The CASE expression checks conditions one by one and returns a result for the first true condition, or a default 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 using CASE expressions.

Execution Table

Step	product_name	price	CASE Evaluation	Result for price_category
1	Pen	5	price < 20 is True	'Cheap'
2	Notebook	25	price < 20 is False; 20 <= price <= 50 is True	'Moderate'
3	Backpack	60	price < 20 is False; 20 <= price <= 50 is False; ELSE	'Expensive'
4	Marker	20	price < 20 is False; 20 <= price <= 50 is True	'Moderate'
5	Eraser	15	price < 20 is True	'Cheap'
6	END			All rows processed, query ends

💡 All rows processed, CASE expression evaluated for each product to assign price_category.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
product_name		Pen	Notebook	Backpack	Marker	Eraser	END
price		5	25	60	20	15
price_category		Cheap	Moderate	Expensive	Moderate	Cheap

Key Moments - 2 Insights

Why does CASE check conditions in order and stop at the first true one?

CASE evaluates WHEN conditions top to bottom and returns the THEN result for the first true condition, skipping the rest. This is shown in execution_table rows 1 and 2 where only the first true condition's result is used.

What happens if none of the WHEN conditions are true and there is no ELSE?

If no WHEN condition matches and ELSE is missing, CASE returns NULL. In our example, ELSE is present, so a default category is always assigned (see row 3).

Visual Quiz - 3 Questions

Look at the execution_table, what is the price_category for 'Marker' at step 4?

A 'Cheap'

B 'Moderate'

C 'Expensive'

D NULL

💡 Hint

Check row 4 in execution_table under 'Result for price_category' column.

At which step does the CASE expression return 'Expensive'?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look for 'Expensive' in the 'Result for price_category' column in execution_table.

If the ELSE clause was removed, what would be the price_category for 'Backpack'?

A NULL

B 'Moderate'

C 'Cheap'

D 'Expensive'

💡 Hint

Refer to key_moments about what happens when no WHEN matches and no ELSE is present.

Concept Snapshot

CASE expression syntax:
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Checks conditions in order, returns first true THEN result,
else returns ELSE or NULL if no match.
Useful for conditional logic inside queries.

Full Transcript

CASE expressions in SQL let you check multiple conditions in order and return different results based on which condition is true first. If none match, you can provide a default with ELSE or get NULL. This helps classify or transform data directly in queries without complex joins or multiple queries. The example query classifies products by price into 'Cheap', 'Moderate', or 'Expensive'. Each product's price is checked against conditions, and the first true condition's label is assigned. This step-by-step evaluation is shown in the execution table. CASE stops checking once a condition is true, making it efficient and clear. If no conditions match and ELSE is missing, the result is NULL. CASE expressions are essential for adding decision logic inside SQL queries simply and clearly.

Practice

1. Why do we need CASE expressions in SQL queries?

easy

A. To change the database schema

B. To create new tables in the database

C. To delete rows from a table

D. To return different values based on conditions within a query

Solution

1. Step 1: Understand the purpose of CASE expressions

   CASE expressions allow SQL queries to return different results depending on conditions you specify.

2. Step 2: Compare with other SQL operations

   Creating tables, deleting rows, or changing schema are done by other SQL commands, not CASE.

3. Final Answer:

   To return different values based on conditions within a query -> Option D

4. Quick Check:

   CASE = conditional output [OK]

Hint: CASE is for conditional results inside queries [OK]

Common Mistakes:

• Thinking CASE creates or deletes tables
• Confusing CASE with schema changes
• Assuming CASE modifies data directly

2. Which of the following is the correct syntax to start a CASE expression in SQL?

easy

A. IF condition THEN result ELSE other END

B. CASE WHEN condition THEN result ELSE other END

C. SWITCH condition THEN result ELSE other END

D. SELECT CASE condition THEN result ELSE other

Solution

1. Step 1: Recall the standard CASE syntax

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

2. Step 2: Identify incorrect options

   IF and SWITCH are not valid SQL CASE keywords; SELECT CASE is incomplete syntax.

3. Final Answer:

   CASE WHEN condition THEN result ELSE other END -> Option B

4. Quick Check:

   CASE syntax = WHEN...THEN...END [OK]

Hint: CASE always uses WHEN...THEN...END structure [OK]

Common Mistakes:

• Using IF instead of CASE in SQL
• Omitting END keyword
• Starting CASE with SELECT

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

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

medium

A. Only salaries below 5000 with label 'Low'

B. Only salaries above 5000 with label 'High'

C. A list of salaries with labels 'High' for salaries above 5000 and 'Low' otherwise

D. An error because CASE cannot be used in SELECT

Solution

1. Step 1: Understand the CASE logic in SELECT

   The CASE checks each Salary; if greater than 5000, label 'High', else 'Low'.

2. Step 2: Check what rows are returned

   The query returns all rows with Salary and corresponding label, no rows are filtered out.

3. Final Answer:

   A list of salaries with labels 'High' for salaries above 5000 and 'Low' otherwise -> Option C

4. Quick Check:

   CASE labels salaries conditionally [OK]

Hint: CASE in SELECT labels rows, does not filter [OK]

Common Mistakes:

• Thinking CASE filters rows like WHERE
• Assuming only 'High' salaries show
• Believing CASE causes syntax error in SELECT

4. Identify the error in this SQL snippet using CASE:

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

medium

A. Missing WHEN keyword after CASE

B. Missing END keyword at the end

C. Incorrect alias usage for SalaryLevel

D. CASE cannot be used in SELECT clause

Solution

1. Step 1: Review CASE syntax

   CASE must be followed by WHEN condition THEN result; here WHEN is missing.

2. Step 2: Check other parts

   END keyword is present; alias usage is correct; CASE is valid in SELECT.

3. Final Answer:

   Missing WHEN keyword after CASE -> Option A

4. Quick Check:

   CASE requires WHEN after CASE [OK]

Hint: CASE always needs WHEN after it [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing END with alias
• Thinking CASE can't be in SELECT

5. You want to assign a grade based on Score in a table:
- Score ≥ 90: 'A'
- Score ≥ 80 and < 90: 'B'
- Score ≥ 70 and < 80: 'C'
- Otherwise: 'F'
Which CASE expression correctly implements this grading logic?

hard

A. CASE WHEN Score ≥ 90 THEN 'A' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 70 THEN 'C' ELSE 'F' END

B. CASE WHEN Score < 70 THEN 'F' WHEN Score < 90 THEN 'C' WHEN Score < 80 THEN 'B' ELSE 'A' END

C. CASE WHEN Score ≥ 70 THEN 'C' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 90 THEN 'A' ELSE 'F' END

D. CASE Score WHEN ≥ 90 THEN 'A' WHEN ≥ 80 THEN 'B' WHEN ≥ 70 THEN 'C' ELSE 'F' END

Solution

1. Step 1: Understand the grading order

   Grades must be checked from highest to lowest score to assign correct grade.

2. Step 2: Analyze each option

   CASE WHEN Score ≥ 90 THEN 'A' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 70 THEN 'C' ELSE 'F' END checks from highest to lowest correctly; CASE WHEN Score < 70 THEN 'F' WHEN Score < 90 THEN 'C' WHEN Score < 80 THEN 'B' ELSE 'A' END incorrectly assigns scores 70-89 to 'C'; CASE WHEN Score ≥ 70 THEN 'C' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 90 THEN 'A' ELSE 'F' END checks in wrong order; CASE Score WHEN ≥ 90 THEN 'A' WHEN ≥ 80 THEN 'B' WHEN ≥ 70 THEN 'C' ELSE 'F' END uses invalid CASE syntax.

3. Final Answer:

   CASE WHEN Score ≥ 90 THEN 'A' WHEN Score ≥ 80 THEN 'B' WHEN Score ≥ 70 THEN 'C' ELSE 'F' END -> Option A

4. Quick Check:

   Order conditions high to low in CASE [OK]

Hint: Check conditions from highest to lowest in CASE [OK]

Common Mistakes:

• Checking conditions in wrong order
• Using CASE with invalid syntax
• Assuming CASE compares values directly without WHEN