How to Use CASE Statement in SQL
💡 Concept Name
CASE Statement in SQL provides conditional logic in SQL queries, allowing different outputs based on specific conditions.
📘 Quick Intro
The CASE statement evaluates conditions and returns a value when the first condition is met. It's similar to if-else logic in programming and works within SELECT, ORDER BY, and WHERE clauses.
🧠 Analogy / Short Story
Think of CASE like a hotel receptionist deciding room types based on guest status. If you're a VIP, you get a suite; if you're regular, a standard room; otherwise, a basic room. The receptionist uses conditions to make decisions. Similarly, SQL uses CASE to decide what output to return based on values.
🔧 Technical Explanation
- 🔁
CASE WHEN condition THEN resultis evaluated top-down until the first true match. - 💬
ELSEhandles default outcomes when no condition matches. - 🧾 Can be used in SELECT, WHERE, ORDER BY, and GROUP BY clauses.
- 🧠 Useful for conditional classification, flags, or computed fields in queries.
- ✅ Syntax supports both simple and searched CASE formats.
🎯 Purpose & Use Case
- ✅ Transform data dynamically (e.g., numeric grades into letter grades).
- ✅ Add derived columns based on logic without changing table schema.
- ✅ Apply conditional filters or sort orders.
- ✅ Simplify reporting and dashboard logic in SQL views.
💻 Real Code Example
-- Use CASE in SELECT
SELECT Name,
Score,
CASE
WHEN Score >= 90 THEN 'A'
WHEN Score >= 75 THEN 'B'
WHEN Score >= 60 THEN 'C'
ELSE 'F'
END AS Grade
FROM Students;
-- Use CASE in WHERE
SELECT * FROM Orders
WHERE
CASE WHEN PaymentStatus = 'Pending' THEN 1
WHEN PaymentStatus = 'Completed' THEN 1
ELSE 0 END = 1;
❓ Interview Q&A
Q1: What is the purpose of the CASE statement in SQL?
A: It allows conditional logic in SQL queries to return different values based on specific conditions.
Q2: Where can you use a CASE statement in SQL?
A: In SELECT, WHERE, ORDER BY, and even GROUP BY clauses.
Q3: What does the ELSE part of a CASE statement do?
A: It handles any condition not explicitly matched by previous WHEN clauses.
Q4: Can CASE be nested?
A: Yes, CASE statements can be nested inside each other.
Q5: What's the difference between simple and searched CASE?
A: Simple CASE compares a single expression; searched CASE evaluates different boolean expressions.
Q6: Is CASE supported in all databases?
A: Most major databases like SQL Server, PostgreSQL, MySQL support it.
Q7: Can CASE return numeric and string values?
A: Yes, as long as all return values are type-compatible.
Q8: Can CASE affect sorting results?
A: Yes, it can be used in ORDER BY to conditionally change sort order.
Q9: Can CASE be used in JOIN conditions?
A: Yes, but it’s less common and may affect performance.
Q10: What are common mistakes when using CASE?
A: Missing ELSE clause, type mismatches, or confusing searched with simple syntax.
📝 MCQs
Q1. What does the CASE statement do?
- Joins tables
- Sorts data
- Implements conditional logic
- Creates tables
Q2. Where can CASE be used?
- Only in SELECT
- Only in WHERE
- SELECT, WHERE, ORDER BY
- GROUP BY only
Q3. Which part is optional in CASE?
- WHEN
- THEN
- ELSE
- END
Q4. What happens if no WHEN matches and ELSE is missing?
- Throws error
- Returns 0
- Returns NULL
- Skips row
Q5. Can CASE return different types?
- Yes, always
- No, should return same or compatible types
- Only strings
- Only numbers
Q6. What is a searched CASE?
- Compares same column values
- Groups records
- Evaluates conditions one by one
- Used only in SELECT
Q7. Which clause uses CASE to sort results?
- WHERE
- JOIN
- GROUP BY
- ORDER BY
Q8. Can CASE be nested?
- No
- Yes
- Only in SQL Server
- Only in MySQL
Q9. What keyword ends a CASE block?
- CLOSE
- FINISH
- STOP
- END
Q10. Which CASE type compares a fixed expression?
- Searched CASE
- Joined CASE
- Simple CASE
- Ordered CASE
💡 Bonus Insight
Use CASE to generate dynamic columns without needing new fields in your database. It’s also powerful for formatting report outputs or scoring logic in analytics queries. Always test CASE for NULL-sensitive comparisons, as NULLs can cause unexpected results.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!