Tutorials›SQL›CASE Statement
🟢 Free Demo
SQL TutorialTopic 15 of 20

CASE Statement

Add if-else logic inside your SQL query

✅ What You Will Learn

›How CASE adds conditional logic to SQL queries
›How to write CASE WHEN ... THEN ... ELSE ... END
›How to use CASE for bucketing numeric values
›How to use CASE inside aggregate functions (conditional aggregation)
›The difference between simple CASE and searched CASE

The CASE statement adds conditional logic to SQL — similar to IF/ELSE in other languages. It evaluates conditions in order and returns a value for the first condition that is true.

CASE is widely used in real analytics for creating category labels, bucketing numbers into ranges, replacing codes with readable text, and building conditional aggregations.

📋 The orders table — CASE adds a computed label column

order_idcustomer_nameproductamount
1001Rahul SharmaLaptop45000
1002Priya VermaMobile Phone18000
1003Amit KumarHeadphones3500
1004Sneha KapoorLaptop52000
1005Vikram SinghTablet28000

Syntax

SQL SYNTAX
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END AS alias_name

Examples

Example 1Categorise orders by value
SELECT customer_name,
       amount,
       CASE
         WHEN amount >= 40000 THEN 'High Value'
         WHEN amount >= 10000 THEN 'Medium Value'
         ELSE 'Low Value'
       END AS order_category
FROM orders;
OUTPUT
customer_name | amount | order_category
--------------|--------|---------------
Rahul Sharma  | 45000  | High Value
Priya Verma   | 18000  | Medium Value
Amit Kumar    | 3500   | Low Value
Sneha Kapoor  | 52000  | High Value
💡

Conditions are checked in order — the first true condition wins. If no condition matches, ELSE provides the default.

Example 2CASE inside COUNT for conditional aggregation
SELECT
  COUNT(CASE WHEN amount >= 40000 THEN 1 END) AS high_value_orders,
  COUNT(CASE WHEN amount < 40000 THEN 1 END) AS other_orders,
  SUM(CASE WHEN product = 'Laptop' THEN amount ELSE 0 END) AS laptop_revenue
FROM orders;
OUTPUT
high_value_orders | other_orders | laptop_revenue
------------------|--------------|---------------
2                 | 2            | 97000
💡

CASE inside COUNT or SUM creates conditional aggregates — count or sum only when a condition is met. This pattern is very powerful for pivot-style reports.

Example 3Replace code values with labels
SELECT customer_name,
       CASE status_code
         WHEN 1 THEN 'Active'
         WHEN 2 THEN 'Inactive'
         WHEN 3 THEN 'Suspended'
         ELSE 'Unknown'
       END AS account_status
FROM customers;
💡

This simpler CASE syntax (without WHEN condition) matches exact values — useful for translating numeric codes into readable labels.

📌 Key Points to Remember

  • ✓CASE is evaluated top to bottom — first matching WHEN wins
  • ✓ELSE is optional but recommended to handle unexpected values
  • ✓CASE can be used in SELECT, WHERE, ORDER BY, and inside aggregates
  • ✓CASE inside COUNT or SUM creates conditional aggregations
  • ✓Always end CASE with END and give it an alias

🏢 Real-World Application

CASE is how you add business logic directly into SQL. Salary bands, customer segments, discount tiers, order priorities — all of these are implemented with CASE. A finance analyst uses CASE to label transactions as "Revenue", "Refund", or "Adjustment" based on transaction type codes. A marketing analyst uses CASE to segment customers into "High Value", "Medium Value", "Low Value" based on total spend. CASE inside SUM() — called conditional aggregation — lets you pivot data without needing a PIVOT operator: SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS electronics_revenue.

⚠️ Common Mistakes to Avoid

WRONGForgetting the END keyword at the close of CASE
FIXEvery CASE expression must be closed with END. CASE WHEN ... THEN ... ELSE ... END. Missing END causes a syntax error.
WRONGNot including an ELSE clause
FIXWithout ELSE, rows that do not match any WHEN condition return NULL. Always include ELSE with a default value to avoid unexpected NULLs in output.
WRONGUsing CASE as a statement instead of an expression
FIXIn SQL, CASE is an expression (it returns a value) — not a statement like in programming languages. It always goes inside SELECT, WHERE, ORDER BY, or aggregate functions.

❓ Frequently Asked Questions

What is the CASE statement in SQL?

CASE is a conditional expression in SQL that returns different values based on conditions. It works like IF-ELSE logic. CASE WHEN condition THEN result WHEN condition2 THEN result2 ELSE default END.

What is the difference between simple CASE and searched CASE?

Simple CASE compares one expression to multiple values: CASE status WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' END. Searched CASE evaluates boolean conditions: CASE WHEN amount > 10000 THEN 'High' ELSE 'Low' END. Searched CASE is more flexible.

Can CASE be used in a WHERE clause?

Yes. CASE can be used anywhere an expression is valid, including WHERE: WHERE (CASE WHEN type = 'A' THEN amount ELSE 0 END) > 1000. However, this is often clearer rewritten as a normal condition.

What is conditional aggregation using CASE?

Conditional aggregation uses CASE inside SUM or COUNT to aggregate only specific rows. Example: SUM(CASE WHEN month = 'January' THEN amount ELSE 0 END) AS jan_revenue. This lets you pivot months into columns without a PIVOT operator.

Can I nest CASE inside another CASE in SQL?

Yes, CASE can be nested inside THEN or ELSE. However, deeply nested CASE becomes hard to read. Consider using a CTE or subquery to break complex logic into steps.

✏️ Practice Exercise

Write a query that adds a "delivery_priority" column: orders above ₹40,000 get "Express", orders between ₹10,000 and ₹40,000 get "Standard", and below ₹10,000 get "Economy".

← PreviousSQL AliasesNext →Handling NULL Values
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.