What are SQL CASE Statements and Why are They Useful?
SQL CASE statements allow you to add if-then logic to queries without having to write complex procedural code. They make it easy to perform conditional aggregation, transform data on the fly, filter record sets, and much more. CASE statements are a concise way to implement decision logic within standard SQL statements.
Using CASE conditional logic directly inside SQL eliminates lots of complex application code you might otherwise have to write. Any app that interacts with a database can benefit. CASE greatly simplifies reporting tasks too.
Overall, mastering CASE statements will let you query data in more flexible ways and reduce reliance on slow application-level processing.
Basic Syntax and Simple Examples of CASE Statements
The syntax for CASE statements in SQL is pretty straightforward:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
The WHEN/THEN clauses allow you to specify conditions, with each separate WHEN checking for a different predicate. The ELSE at the end handles anything that doesn’t meet the other criteria.
Here is a simple example that classifies numbers as small, medium, or large:
SELECT
num,
CASE
WHEN num < 10 THEN 'Small'
WHEN num < 100 THEN 'Medium'
ELSE 'Large'
END AS size
FROM numbers;
We can use the COALESCE function along with CASE to handle null values by providing a default:
SELECT
name,
COALESCE(
CASE
WHEN grade >= 90 THEN 'A'
WHEN grade >= 80 THEN 'B'
ELSE 'C'
END,
'N/A'
) AS letter_grade
FROM students;
As you can see, CASE statements make it easy to conditionally alter results within a standard query.
Using CASE Statements in WHERE Clauses to Filter Records
CASE can be extremely valuable for filtering records with complex conditions in WHERE clauses.
For example, let’s filter product listings differently depending on product type:
SELECT *
FROM listings
WHERE
CASE product_type
WHEN 'electronics' THEN price < 100
WHEN 'clothing' THEN price < 50
ELSE price < 10
END
We were able to define custom price filters for different product categories, rather than just using one blanket condition.
You can also filter differently based on date ranges:
SELECT *
FROM web_logs
WHERE
CASE
WHEN created_at >= CURRENT_DATE - INTERVAL '7 days' THEN views > 100
WHEN created_at >= CURRENT_DATE - INTERVAL '30 days' THEN views > 500
ELSE views > 1000
END
The key thing to remember is that CASE in WHERE always results in either TRUE, FALSE, or NULL. So make sure your logic simplifies down to that.
CASE Expressions for Transforming Data in SELECT Statements
In addition to the filtering examples we just covered, CASE can transform data during selection:
SELECT
name,
CASE status
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Inactive'
ELSE 'Unknown'
END AS status
FROM accounts;
We were able to turn magic status numbers into readable strings!
You can shape or concatenate data in other creative ways too:
SELECT
name,
CASE
WHEN LENGTH(title) > 30 THEN CONCAT(SUBSTRING(title, 1, 30), '...')
ELSE title
END AS short_title
FROM books;
In this case, we truncated any book titles longer than 30 characters by tacking on ellipses.
These examples demonstrate the flexibility of CASE for handling values during result processing.
Handling Nulls, ELSE, and Other Advanced Features
A common requirement is substituting values when columns contain nulls. As shown earlier, combining CASE and COALESCE provides an easy way to tackle this:
SELECT
name,
COALESCE(middle_name, CASE WHEN middle_name IS NULL THEN '(none)' END)
FROM customers;
The ELSE clause in CASE defaults to null, but it’s sometimes useful to specify a different value:
SELECT
name,
CASE
WHEN grade >= 90 THEN 'A'
WHEN grade >= 80 THEN 'B'
ELSE 'No pass'
END AS letter_grade
FROM students;
And while each CASE statement can only include one ELSE, you can chain various CASE expressions together in nested fashion for more advanced logic.
CASE Statement Performance Considerations
CASE statements are very versatile, however overusing them can negatively impact query optimization in some database systems.
Keep conditional logic simple whenever possible, and test regularly to catch performance issues before they affect end users. If queries do slow down, try to isolate whether CASE is the actual culprit.
In most situations CASE performs well, but it’s always good to be aware of the tradeoffs and test builds thoroughly!
FAQ
What is the difference between CASE statements and IF…THEN logic?
CASE statements allow conditional logic directly in SQL without having to implement procedural code like IF…THEN programming blocks. So CASE keeps things concise and set-based.
Can CASE statements be used with aggregate functions?
Absolutely! CASE works well to define conditions over groups of records before aggregation.
What other SQL dialects support CASE statements?
Nearly every SQL variant supports CASE expressions, including T-SQL for Microsoft SQL Server, PL/pgSQL in PostgreSQL, and beyond.
Conclusion
SQL CASE statements enable you to selectively display data and shape result sets on the fly based on custom conditions. They provide a simple way add if-then logic without having to rely on slow procedural code or external application processing.
As you add advanced querying features like window functions, stored procedures, etc, don’t forget the trusty CASE statement. Mastering various forms of conditional logic will make your SQL capabilities far more flexible!
