Nested CASE expressions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a CASE expression in SQL?

A CASE expression lets you perform conditional logic in SQL queries. It works like an IF-THEN-ELSE statement, returning different values based on conditions.

intermediate

What does it mean to have a nested CASE expression?

A nested CASE expression is when you put one CASE expression inside another. This helps handle multiple layers of conditions in a query.

intermediate

Write a simple example of a nested CASE expression in SQL.

SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN CASE WHEN score >= 85 THEN 'B+' ELSE 'B' END ELSE 'C' END AS grade FROM students;

intermediate

Why use nested CASE expressions instead of multiple separate CASE statements?

Nested CASE expressions keep related conditions together, making queries easier to read and maintain. They also allow more precise control over complex decision logic.

advanced

Can a nested CASE expression return different data types in its branches?

No. All return values in a CASE expression, including nested ones, should be of compatible data types to avoid errors.

What keyword starts a CASE expression in SQL?

A CASE

B IF

C WHEN

D SELECT

The CASE expression always starts with the keyword CASE.

In a nested CASE, where is the inner CASE placed?

A After the END keyword

B Before the outer CASE

C Inside the THEN or ELSE part of the outer CASE

D Outside the SELECT statement

The inner CASE is placed inside the THEN or ELSE part of the outer CASE expression.

What happens if none of the WHEN conditions in a CASE expression match and there is no ELSE?

A Returns 0

B Returns NULL

C Returns an error

D Returns the first WHEN value

If no WHEN condition matches and ELSE is missing, CASE returns NULL.

Which of these is a valid use of nested CASE?

A CASE WHEN x > 10 THEN 'High' CASE WHEN x > 20 THEN 'Very High' END ELSE 'Low' END

B CASE x > 10 THEN 'High' ELSE 'Low' END

C CASE WHEN x > 10 THEN 'High' ELSE CASE WHEN x > 20 THEN 'Very High' END

D CASE WHEN x > 10 THEN CASE WHEN x > 20 THEN 'High' ELSE 'Medium' END ELSE 'Low' END

Option D correctly nests a CASE inside the THEN part of the outer CASE.

Can nested CASE expressions be used in the SELECT clause?

A Yes, to compute complex conditional values

B No, only in WHERE clauses

C No, only in ORDER BY clauses

D Only in JOIN conditions

Nested CASE expressions are often used in SELECT to compute complex conditional results.

Explain how a nested CASE expression works in SQL and give a simple example.

Think of CASE inside CASE like decision steps inside other decision steps.

Describe why nested CASE expressions are useful and what to watch out for when using them.

Consider both benefits and challenges of nesting conditions.