CodeCapsule

SQL CASE Expression

by

Here is a clear, practical, and complete guide to the SQL CASE expression, one of the most powerful tools for conditional logic inside SQL queries.

✅ What the CASE Expression Does

CASE lets you apply IF / ELSE style logic inside SQL.

It returns a value based on conditions and is often used in:

  • SELECT lists

  • ORDER BY

  • GROUP BY

  • UPDATE statements

  • Aggregations

🔹 Basic Syntax (Simple CASE)

Compares one expression to different values:

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

🔹 Searched CASE (most common)

Checks Boolean conditions:

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

📌 Practical Examples

1. Categorize numeric values

SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'D'
END AS grade
FROM students;

2. Replace NULL values

SELECT name,
CASE
WHEN email IS NULL THEN 'No email'
ELSE email
END AS email_status
FROM users;

(Similar to COALESCE, but more flexible.)

3. Conditional aggregation

Count only active users:

SELECT
COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_users
FROM users;

4. Use CASE in ORDER BY

Sort VIP users first:

SELECT name, is_vip
FROM customers
ORDER BY
CASE WHEN is_vip = 1 THEN 0 ELSE 1 END,
name;

5. Conditional UPDATE

UPDATE employees
SET salary =
CASE
WHEN performance = 'excellent' THEN salary * 1.20
WHEN performance = 'good' THEN salary * 1.10
ELSE salary
END;

6. Dynamic grouping / bucketing

SELECT
CASE
WHEN age < 18 THEN 'Minor'
WHEN age BETWEEN 18 AND 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group,
COUNT(*) AS count
FROM people
GROUP BY age_group;

🧠 Important Notes

✔ CASE returns the first matching condition, then stops
✔ All results must be compatible data types
✔ ELSE is optional — default is NULL
✔ Works the same in PostgreSQL, MySQL, SQL Server, Oracle

🏎 Performance Tips

✔ Keep CASE expressions simple for readability
✔ Put most-likely conditions first for performance
✔ Use indexes on columns inside CASE conditions when possible
✔ For complex logic, consider creating computed columns or views

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *