CASE in WHERE clause in SQL - Step-by-Step Execution

Concept Flow - CASE in WHERE clause

Start Query Execution

↓

Evaluate WHERE Clause

↓

CASE Expression Evaluated

↓

Compare CASE Result to Condition

↓

Row Included if Condition True

↓

Next Row or End

The query checks each row by evaluating the CASE expression inside the WHERE clause, then includes the row if the condition is true.

Execution Sample

SQL

SELECT * FROM employees
WHERE CASE WHEN department = 'Sales' THEN salary > 50000
           WHEN department = 'HR' THEN salary > 40000
           ELSE salary > 30000 END = TRUE;

This query selects employees based on salary thresholds that depend on their department using CASE in the WHERE clause.

Execution Table

Step	Row Data (department, salary)	CASE Evaluation	Condition Result	Include Row?
1	('Sales', 60000)	salary > 50000 -> 60000 > 50000 = TRUE	TRUE	Yes
2	('Sales', 45000)	salary > 50000 -> 45000 > 50000 = FALSE	FALSE	No
3	('HR', 42000)	salary > 40000 -> 42000 > 40000 = TRUE	TRUE	Yes
4	('HR', 39000)	salary > 40000 -> 39000 > 40000 = FALSE	FALSE	No
5	('IT', 35000)	salary > 30000 -> 35000 > 30000 = TRUE	TRUE	Yes
6	('IT', 29000)	salary > 30000 -> 29000 > 30000 = FALSE	FALSE	No
7	End of rows	-	-	-

💡 All rows processed; query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
department	N/A	Sales	Sales	HR	HR	IT	IT	N/A
salary	N/A	60000	45000	42000	39000	35000	29000	N/A
CASE result	N/A	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	N/A
Include row?	N/A	Yes	No	Yes	No	Yes	No	N/A

Key Moments - 2 Insights

Why does the CASE expression return TRUE or FALSE instead of a value?

Because the CASE in the WHERE clause is used to evaluate conditions that result in TRUE or FALSE to decide if a row should be included, as shown in execution_table rows 1-6.

What happens if none of the WHEN conditions match in the CASE?

The ELSE part is evaluated, as in row 5 and 6 where department is 'IT', so the ELSE condition salary > 30000 is checked.

Visual Quiz - 3 Questions

Look at the execution_table, what is the CASE result for the row ('HR', 39000)?

A TRUE

B FALSE

C NULL

D Error

💡 Hint

Check row 4 in the execution_table under CASE Evaluation and Condition Result.

At which step does the ELSE condition get evaluated?

A Step 1

B Step 3

C Step 5

D Step 6

💡 Hint

Look at the department value and CASE Evaluation in steps 5 and 6.

If the salary for ('Sales', 45000) changed to 55000, what would be the Include Row? value at step 2?

A Yes

B No

C Depends on ELSE

D Error

💡 Hint

Refer to step 2 in execution_table and consider the condition salary > 50000.

Concept Snapshot

CASE in WHERE clause syntax:
WHERE CASE
  WHEN condition1 THEN condition_result1
  WHEN condition2 THEN condition_result2
  ELSE condition_result_default
END = TRUE;

It evaluates conditions per row and returns TRUE/FALSE to filter rows.
Use CASE to apply different filters based on row data.

Full Transcript

This visual execution shows how a SQL query uses CASE inside the WHERE clause to filter rows differently based on department and salary. Each row is checked: the CASE expression evaluates conditions for that row's department and salary, returning TRUE or FALSE. Rows with TRUE are included in the result. The ELSE clause handles departments not explicitly listed. The execution table traces each row's evaluation step by step, showing how the CASE expression guides which rows pass the filter.