Conditional Logic in SQL: A Guide to the CASE Statement

Databases often require logic: “if this, then that.” In SQL, this is handled using the CASE statement. It’s SQL’s native branching mechanism, much like if-else or switch in programming.

This guide explores the two primary types of CASE statements—simple and searched—and demonstrates how to use them in actual business scenarios like data classification, price adjustments, and bonus calculations.

Practical Examples of CASE

Simple CASE:

SELECT
  CASE season
    WHEN 'Spring' THEN '🌸'
    WHEN 'Summer' THEN '☀️'
    ELSE '❓'
  END AS icon;

Searched CASE:

CASE
  WHEN price > 1000 THEN 'Premium'
  WHEN price > 500 THEN 'Standard'
  ELSE 'Budget'
END

Discount Calculation:

SELECT category, price,
  CASE
    WHEN category = 'Gifts' AND price <= 50 THEN price * 0.5
    WHEN category = 'Electronics' AND price >= 1500 THEN price * 0.7
    ELSE price
  END AS discounted_price
FROM products;

Bonus Allocation with Nested CASE:

CASE
  WHEN role = 'Manager' THEN
    CASE
      WHEN years >= 5 THEN salary * 0.10
      ELSE salary * 0.05
    END
  WHEN role = 'Developer' AND years >= 3 THEN salary * 0.08
  ELSE 0
END

FAQ

What types of CASE expressions are available in SQL?

Simple CASE (value-based) and Searched CASE (condition-based).

Can CASE be used in WHERE and ORDER BY?

Yes. It's fully compatible with filtering, sorting, and updating operations.

What happens if all conditions fail?

If there’s no ELSE clause, the result is NULL.

Does CASE support nesting?

Absolutely. You can nest CASE statements to build tiered or hierarchical logic.

Conclusion

SQL CASE expressions offer built-in support for conditional logic that works across major database systems. They simplify query design and allow you to centralize your logic within SQL itself.

Read the full guide SQL CASE Statement: Definitive Guide.