Conditional Logic Using CASE Expressions

SQL isn’t just about selecting and aggregating data—it can also apply conditional logic to transform or categorize results. That’s where the CASE expression comes in.

CASE lets you create new values based on conditions, similar to an IF/ELSE statement in programming.

Basic CASE Syntax

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

You can use CASE in a SELECT statement, inside aggregation functions, or even in ORDER BY.

Example: Categorizing Paintings by Period

Suppose you want to group paintings into Renaissance, 19th Century, or Modern categories based on YearCreated.

SELECT Title,
	   Artist,
	   YearCreated,
	   CASE
		   WHEN YearCreated BETWEEN 1400 AND 1600 THEN 'Renaissance'
		   WHEN YearCreated BETWEEN 1800 AND 1899 THEN '19th Century'
		   WHEN YearCreated >= 1900 THEN 'Modern'
		   ELSE 'Other'
	   END AS PeriodCategory
FROM Gallery.Paintings;

SELECT Title,
	   Artist,
	   YearCreated,
	   CASE
		   WHEN YearCreated BETWEEN 1400 AND 1600 THEN 'Renaissance'
		   WHEN YearCreated BETWEEN 1800 AND 1899 THEN '19th Century'
		   WHEN YearCreated >= 1900 THEN 'Modern'
		   ELSE 'Other'
	   END AS PeriodCategory
FROM Gallery.Paintings;

Result:

📌 Important Note: Best practice is to always include an ELSE default condition for handling any values that fall outside all WHEN conditions. While the CASE expression without ELSE will complete without error, this can unintentionally exclude records from results.

• If you don’t want to assign a value when no conditions are met, use ELSE NULL.
• If it’s your intention to exclude records that don’t meet specific conditions, use a WHERE condition to make that explicitly clear.

Example: Using CASE in ORDER BY

You can also use CASE to customize sort order.

This query will sort results by PeriodCategory, then by Artist.

SELECT Title,
	   Artist,
	   YearCreated,
	   CASE
		   WHEN YearCreated BETWEEN 1400 AND 1600 THEN 'Renaissance'
		   WHEN YearCreated BETWEEN 1800 AND 1899 THEN '19th Century'
		   WHEN YearCreated >= 1900 THEN 'Modern'
		   ELSE 'Other'
	   END AS PeriodCategory
FROM Gallery.Paintings
ORDER BY CASE
			 WHEN YearCreated BETWEEN 1400 AND 1600 THEN 'Renaissance'
			 WHEN YearCreated BETWEEN 1800 AND 1899 THEN '19th Century'
			 WHEN YearCreated >= 1900 THEN 'Modern'
			 ELSE 'Other'
		 END,
		 Artist;

SELECT Title,
	   Artist,
	   YearCreated,
	   CASE
		   WHEN YearCreated BETWEEN 1400 AND 1600 THEN 'Renaissance'
		   WHEN YearCreated BETWEEN 1800 AND 1899 THEN '19th Century'
		   WHEN YearCreated >= 1900 THEN 'Modern'
		   ELSE 'Other'
	   END AS PeriodCategory
FROM Gallery.Paintings
ORDER BY CASE
			 WHEN YearCreated BETWEEN 1400 AND 1600 THEN 'Renaissance'
			 WHEN YearCreated BETWEEN 1800 AND 1899 THEN '19th Century'
			 WHEN YearCreated >= 1900 THEN 'Modern'
			 ELSE 'Other'
		 END,
		 Artist;

Notice that the CASE statement ending on line 9 includes an alias AS PeriodCategory to define the column name in the results, but the CASE statement ending on line 16 does not include an alias. This is intentional.

📌 Important Note: The ORDER BY clause will return an error when an alias is included.

Example: Using CASE with GROUPING()

When using ROLLUP, CUBE, or GROUPING SETS, subtotal and total rows show up with NULL values. You can use CASE with the GROUPING() function to replace those NULLs with descriptive labels.

SELECT 
	CASE 
		WHEN GROUPING(CountryOfOrigin) = 1 THEN 'All Countries'
		ELSE CountryOfOrigin
	END AS CountryLabel,
	CASE
		WHEN GROUPING(Artist) = 1 THEN 'All Artists'
		ELSE Artist
	END AS ArtistLabel,
	COUNT(1) AS PaintingCount
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy','Netherlands','Spain')
GROUP BY ROLLUP (CountryOfOrigin, Artist)
ORDER BY CountryOfOrigin,
		 Artist;

SELECT 
	CASE 
		WHEN GROUPING(CountryOfOrigin) = 1 THEN 'All Countries'
		ELSE CountryOfOrigin
	END AS CountryLabel,
	CASE
		WHEN GROUPING(Artist) = 1 THEN 'All Artists'
		ELSE Artist
	END AS ArtistLabel,
	COUNT(1) AS PaintingCount
FROM Gallery.Paintings
WHERE CountryOfOrigin IN ('Italy','Netherlands','Spain')
GROUP BY ROLLUP (CountryOfOrigin, Artist)
ORDER BY CountryOfOrigin,
		 Artist;

Result:

This makes reports more user-friendly by replacing ambiguous NULL values with meaningful text.

Key Takeaways

• CASE lets you apply conditional logic directly in SQL queries.
• Use CASE in SELECT, ORDER BY, or aggregation expressions to categorize or transform data.
• Combine CASE with GROUPING() to label subtotal and total rows when using ROLLUP, CUBE, or GROUPING SETS.
• This technique improves readability and is highly useful for reporting and dashboards.