CASE Expression

Overview

The CASE expression implements conditional if-then-else logic directly inside SQL queries, enabling dynamic data classification.

Learning Objectives

• Implement Simple CASE switch checks.
• Build range conditional validations using Searched CASE.
• Nest CASE structures inside aggregations for conditional totals.

Detailed Concept Explanation

SQL's CASE works like standard conditional structures in programming languages. It evaluates expressions in order, returning the output of the first TRUE condition.

Simple CASE

Compares a column or expression against fixed matching values:

CASE role_code
  WHEN 1 THEN 'Admin'
  WHEN 2 THEN 'Manager'
  ELSE 'User'
END

Searched CASE

Evaluates completely separate range/logical predicates:

CASE 
  WHEN price > 100 THEN 'Premium'
  WHEN price BETWEEN 50 AND 100 THEN 'Standard'
  ELSE 'Budget'
END

Code Examples

SQL

-- Classify employee experience groups
SELECT name, salary,
  CASE 
    WHEN salary >= 100000 THEN 'Tier 1'
    WHEN salary >= 60000 THEN 'Tier 2'
    ELSE 'Tier 3'
  END AS salary_tier
FROM employees;

-- Count items by status category (Conditional Aggregation)
SELECT 
  SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS shipped_count,
  SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending_count
FROM orders;

Best Practices

• Include ELSE: Always provide an ELSE fallback inside your CASE statement. If no conditions match and ELSE is missing, the expression returns NULL.
• Avoid Deep Nesting: Deeply nested CASE statements degrade performance and are hard to debug. Keep conditions modular.

Interview Perspective

Interactive Challenges

Summary

CASE statements enable conditional data styling and routing, making it easy to categorize attributes and write conditional sums.