Conditional Logic

SQL is a declarative language, but it still needs a way to handle branching logic. The CASE expression is SQL’s version of the if-else or switch-case statements found in other programming languages.

Definition: The CASE Expression

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Context: Why Use CASE?

The CASE statement is primarily used to:

1. Categorize Data: (e.g., Turning an “Age” integer into “Minor”, “Adult”, or “Senior”).
2. Transform Values: (e.g., Turning 1 and 0 flags into Yes and No).
3. Perform Safe Division: (e.g., Checking if a denominator is zero before dividing).
4. Pivot Data: (Used in advanced reporting to turn rows into columns).

Detailed Explanation: Two Types of CASE

1. Simple CASE

The Simple CASE expression compares an expression to a set of simple expressions to determine the result.

Simple CASE Syntax

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

2. Searched CASE

The Searched CASE expression evaluates a set of Boolean expressions to determine the result. This is much more flexible as you can use complex logic (like AND, OR, <, >) in each WHEN clause.

Searched CASE Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Example: Employee Bonuses

We have an Employees table:

Name	PerformanceScore	Salary
Alice	5	100000
Bob	3	80000
Charlie	1	70000

Task: Calculate a bonus based on performance.

• Score 5: 20% bonus
• Score 3 or 4: 10% bonus
• Others: No bonus

Searched CASE Example

SELECT
  Name,
  PerformanceScore,
  CASE
      WHEN PerformanceScore = 5 THEN Salary * 0.20
      WHEN PerformanceScore IN (3, 4) THEN Salary * 0.10
      ELSE 0
  END AS BonusAmount
FROM Employees;

Output

Name	PerformanceScore	BonusAmount
Alice	5	20000
Bob	3	8000
Charlie	1	0

Under the Hood: Expression vs. Statement

It is important to remember that in SQL, CASE is an expression, not a statement.

• A statement performs an action (like DELETE).
• An expression returns a value (like 2 + 2).

Because it is an expression, you can use it anywhere a value is allowed: in the SELECT list, in WHERE clauses, and even in ORDER BY clauses.

Performance Tip: Short-Circuiting

The SQL engine evaluates WHEN clauses in the order they are written. Once a condition is met, it skips the rest. To optimize your query:

• Place the most likely condition first to minimize the number of checks the engine has to perform for the majority of your rows.

Caution

Always include an ELSE clause. If you don’t, and no condition matches, the expression returns NULL, which might break downstream logic or calculations.