SQL Case

SQL CASE Statement

The CASE statement adds conditional logic to SQL queries. It works like an IF-THEN-ELSE expression.

Basic CASE Syntax

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

Example: Categorize Products by Price

SELECT name,
       price,
       CASE
         WHEN price > 500 THEN 'Expensive'
         WHEN price BETWEEN 100 AND 500 THEN 'Mid-range'
         ELSE 'Budget'
       END AS price_category
FROM products;

Searched CASE Example

CASE can evaluate logical expressions directly:

SELECT name,
       CASE
         WHEN stock = 0 THEN 'Out of stock'
         WHEN stock < 10 THEN 'Low stock'
         ELSE 'In stock'
       END AS stock_status
FROM products;

Using CASE in ORDER BY

CASE can control custom sorting logic.

SELECT name, status
FROM customers
ORDER BY
  CASE
    WHEN status = 'active' THEN 1
    WHEN status = 'trial' THEN 2
    ELSE 3
  END;

Using CASE with Aggregation

CASE can be combined with aggregate functions.

SELECT
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM customers;

Using CASE in UPDATE

UPDATE products
SET price =
  CASE
    WHEN category = 'Electronics' THEN price * 1.10
    ELSE price
  END;

Important Notes

• CASE returns a single value
• All result expressions must be compatible data types
• ELSE is optional (returns NULL if omitted and no condition matches)

Common Mistakes

• Forgetting END
• Mixing incompatible data types in THEN/ELSE
• Confusing CASE with procedural IF statements

Next Step

Continue with SQL Null Functions to learn how to handle NULL values more effectively.