CASE WHEN

This lesson on CASE WHEN is hands-on and example-driven. You will learn how to use the SQL CASE statement to apply complex, row-level conditional logic within your queries. You will be able to categorize data, calculate conditional values like bonuses, and alias the resulting output column. This tool is essential for transforming raw data into meaningful business metrics.

What You'll Be Able To Do

• Construct a basic CASE statement using WHEN, THEN, and END clauses.
• Chain multiple WHEN conditions to create layered decision logic.
• Apply the BETWEEN operator efficiently for range filtering within a condition.
• Calculate new column values based on conditional criteria (e.g., salary raises).
• Alias the output of a CASE statement for a readable column name.

Detailed Concept Walkthrough

1. Basic CASE Syntax and Structure

The CASE statement acts like an IF/THEN/ELSE block in SQL, allowing you to evaluate conditions sequentially and return a corresponding result. It must always conclude with the END keyword.

• Mechanism: SQL evaluates WHEN conditions from top to bottom; the first condition that evaluates to TRUE is executed, and the process stops immediately.
• Syntax Rule: Every CASE statement must contain at least one WHEN/THEN pair and must terminate with END.
• Best Practice: Always include an ELSE clause to handle rows that do not meet any specified WHEN condition, preventing unexpected NULL results.
• Under the Hood: CASE is evaluated row-by-row during the SELECT operation, making it a powerful tool for data transformation before output.

SELECT
  CASE WHEN age <= 30 THEN 'Young' ELSE 'Mature' END AS age_bracket
FROM employee_demographics;

Key Takeaway: CASE evaluates conditions sequentially and returns the result of the first TRUE condition encountered.

2. Layering Conditions and Ranges

Multiple WHEN clauses allow you to build complex, layered logic, ensuring that data falls into distinct, non-overlapping categories. The BETWEEN operator simplifies range checks.

• Execution Flow: If the first WHEN condition is false, the database moves immediately to evaluate the second WHEN condition, and so on.
• Mechanism: BETWEEN is inclusive; A BETWEEN B AND C is shorthand for A >= B AND A <= C.
• Best Practice: Order WHEN clauses from most specific to least specific to ensure correct categorization, although the sequential evaluation handles overlaps.
• Nuance: The conditions within WHEN clauses use the same comparison operators (<=, >=, =) as the WHERE clause.

SELECT
  CASE WHEN age <= 30 THEN 'Young'
       WHEN age BETWEEN 31 AND 50 THEN 'Old'
       ELSE 'Senior' END
FROM employee_demographics;

Key Takeaway: Chain WHEN clauses to categorize data into distinct groups, using BETWEEN for concise range definitions.

3. Conditional Calculations and Aliasing

The THEN clause can execute mathematical operations or reference other columns based on the condition, not just return static strings. The entire CASE block must be aliased to name the resulting column.

• Mechanism: The calculation inside the THEN clause is performed only for the rows that satisfy the preceding WHEN condition.
• Syntax Rule: The alias (using AS) must be placed immediately after the END keyword of the CASE statement.
• Application: CASE statements can apply logic based on columns that are not included in the final SELECT list (e.g., using dept_id to calculate a bonus).
• Arithmetic: Basic arithmetic operators (+, *, -) and percentage math are fully supported within the THEN clause.

SELECT salary,
  CASE WHEN dept_id = 6 THEN salary * 0.10
       ELSE 0 END AS bonus_amount
FROM employee_salary;

Key Takeaway: Use the THEN clause for dynamic calculations and always alias the CASE statement using END AS column_name.

Topics Covered in CASE WHEN

1. CASE Statement Basics (0:28 - 1:04) — Introduces the core syntax including CASE, WHEN, THEN, and END keywords.
2. Multiple WHEN Conditions (1:32 - 2:00) — Demonstrates how to layer logic by chaining several WHEN clauses sequentially.
3. BETWEEN Operator (1:41 - 1:47) — Explains BETWEEN as a concise, inclusive shorthand for checking if a value falls within a range.
4. Aliasing CASE Results (2:37 - 2:47) — Shows how to name the resulting output column using the AS keyword after the END clause.
5. Logic to Calculations (4:37 - 6:05) — Illustrates performing mathematical operations within the THEN clause for conditional raises or adjustments.
6. Conditional Bonuses (7:37 - 8:06) — Applies CASE logic based on a non-display column like department ID to determine a bonus.
7. Next Topic Preview (8:38 - 8:38) — Briefly mentions that subqueries will be the focus of the subsequent lesson.

SQL Cheat Sheet

• CASE WHEN condition THEN result END — Defines conditional logic for row transformation

  CASE WHEN age > 60 THEN 'Senior' END
  

• WHEN A BETWEEN B AND C — Inclusive shorthand for range filtering

  WHEN age BETWEEN 31 AND 50 THEN 'Middle'
  

• END AS column_name — Names the output column of the CASE

  END AS age_bracket
  

• THEN salary + (salary * 0.05) — Performs calculation based on condition

  THEN salary * 1.05
  

• WHEN dept_id = 6 — Applies logic based on non-display column

  WHEN dept_id = 6 THEN 'Bonus'
  

Comparison Table

Common Pitfalls

• Mistake: Forgetting the END keyword to close the statement. Avoid: Always terminate the entire CASE structure with END.
• Mistake: Assuming conditions are evaluated simultaneously. Avoid: CASE stops evaluating after the first WHEN condition is TRUE.
• Mistake: Placing the alias before the END keyword. Avoid: The AS alias must follow the closing END keyword.
• Mistake: Using ranges that overlap without careful ordering. Avoid: Order WHEN clauses from most specific to least specific.

FAQs

• Is the ELSE clause mandatory in a CASE statement? No, but if omitted, any row that fails all WHEN conditions will return NULL for that column.
• Can I use CASE statements in the WHERE clause? Yes, but it is often more efficient to use standard comparison operators or WHERE logic directly.
• What happens if two WHEN conditions are true for the same row? Only the result from the first WHEN condition listed in the statement is returned, as evaluation stops immediately upon the first match.