What is a CASE statement in SQL?

A CASE statement in SQL is a conditional expression that allows you to add logic to your queries by evaluating conditions and returning specific values based on the results. It works similarly to if-else statements in programming languages, enabling dynamic decision-making within SQL queries. CASE statements are often used in SELECT, WHERE, ORDER BY, and even UPDATE clauses to transform data or control output based on specific criteria. They are particularly useful for categorizing data, handling NULL values, or creating calculated columns without modifying the underlying data.

CASE statements come in two forms: simple and searched. The simple CASE evaluates a single expression against multiple possible values. For example, CASE status WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' END checks the value of status and returns corresponding labels. The searched CASE evaluates multiple independent conditions using Boolean expressions, like CASE WHEN salary > 100000 THEN 'High' WHEN salary > 50000 THEN 'Medium' ELSE 'Low' END, which categorizes salaries into tiers. Both forms support an optional ELSE clause to handle unmatched cases, preventing unexpected NULL values in results. Without ELSE, the statement defaults to returning NULL if no conditions are met.

Developers use CASE statements to solve practical problems. For instance, in a SELECT clause, you might dynamically label order statuses (e.g., “Shipped,” “Pending”) based on a numeric code stored in a database. In an ORDER BY clause, you could prioritize sorting rows by specific criteria, such as placing “Urgent” tasks at the top of a list. CASE also simplifies aggregations: SUM(CASE WHEN department = 'Sales' THEN revenue ELSE 0 END) calculates total sales revenue without requiring a separate query. By embedding conditional logic directly in SQL, CASE statements reduce the need for post-processing data in application code, improve query efficiency, and make complex filtering or transformation tasks more readable. Common pitfalls include overlapping conditions (which can cause unexpected results) and omitting ELSE, so thorough testing is essential.