This is the fifth in a series of articles aimed at introducing the basics of SQL to Excel users. This article shows how to use the IF THEN function in SQL. If you’re interested in the other articles, check them out here:
- Introduction to SQL for Excel Users
- What is the equivalent to VLOOKUP in SQL?
- How to create Pivot Tables in SQL Server
- Simple Data Validation in SQL
There’ll be more articles in this series, so keep an eye on the blog in the coming weeks!
Excel IF statement: a recap
The IF function is one of the most commonly used in Excel. It allows you to perform a comparison against a value in a cell and return a value if the comparison is true, and another value if it’s false.
There are several scenarios when the IF function can be used:
- When we want to swop a text value for a numerical value – e.g. IF(A2=”Yes”, 1, 0)
- When we want to swop a numerical value for a text description – e.g. IF(A2>100, “High”, “Low”)
- When we want to perform a certain calculation when the target value meets a condition – e.g. IF(B2>A2, B2-A2, 0)
IF functions can get a bit nasty when we introduce lots of nesting. For example, the following function to determine the commission to use based on total revenue is difficult to troubleshoot with all those brackets (and we’ve all seen worse than this…)
=IF(B2>15000,20%,
IF(B2>12500,17.5%,
IF(B2>10000,15%,
IF(B2>7500,12.5%,
IF(B2>5000,10%,0)))))Thankfully, Excel introduced the IFS function with Excel 2019 which can be used to replace nested IF functions. The same formula above can be written with IFS as:
=IFS(
B2>15000,20%,
B2>12500,17.5%,
B2>10000,15%,
B2>7500,12.5%,
B2>5000,10%,
true,0)(Of course, you could also argue that this type of calculation should really be done with a lookup table and with the XLOOKUP function.)
After that quick re-cap of the use of the IF function in Excel, let’s look at how we can mimic the same behavior when writing queries in SQL.
Basic CASE Expression in SQL Server
The CASE expression works in the same way as the IF and IFS function in Excel. It goes through one or more conditions and returns a value when the first condition is met. So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
The basic usage of the CASE expression is to use it in a SELECT statement with a simple equality check. In the following example, we’ve used it to change the value of the style categories to make them easier to understand.
SELECT
[ProductNumber],
[Name],
CASE Style
WHEN 'U' THEN 'Unisex'
WHEN 'W' THEN 'Women'
WHEN 'M' THEN 'Men'
ELSE 'N/A'
END AS 'Category',
FROM [AdventureWorks2019].[Production].[Product]
Order By [Name]
Here is a description of the syntax:
CASE input_expression
WHEN when_expression_1 THEN result_expression_1
WHEN when_expression_n THEN result_expression_n
[ ELSE else_result_expression ]
ENDAs you can see, the syntax is quite simple. It says “compare the input expression with one or more expressions, returning the corresponding result if TRUE, or the else expression if FALSE.”
- input_expression: the expression to use in the comparison
- when_expression: a simple expression to which input_expression is compared
- result_expression: the expression returned when input_expression equals when_expression evaluates to TRUE
- else_result_expression: the expression returned if no comparison operation evaluates to TRUE.
More examples of the CASE expression
Using Comparison Values
The example below builds on the simple expression above by including comparison values in the when_expression to include a text comment for the price range of a product.
SELECT
[ProductNumber],
[Name],
CASE
WHEN ListPrice = 0 THEN 'Manufacturing Item - Not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN '$50 to $250'
WHEN ListPrice >= 250 and ListPrice < 500 THEN '$250 to $500'
WHEN ListPrice >= 500 and ListPrice < 1000 THEN '$500 to $1000'
WHEN ListPrice >= 1000 and ListPrice <= 2000 THEN '$1000 to $2000'
ELSE 'Over $2000'
END As 'Price Range'
FROM [AdventureWorks2019].[Production].[Product]
Order By [Name]
Group By a CASE statement
We can use a GROUP BY expression to show how many products fall into each price range.
SELECT
CASE WHEN ListPrice = 0 THEN 'Manufacturing Item - Not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN '$50 to $250'
WHEN ListPrice >= 250 and ListPrice < 500 THEN '$250 to $500'
WHEN ListPrice >= 500 and ListPrice < 1000 THEN '$500 to $1000'
WHEN ListPrice >= 1000 and ListPrice <= 2000 THEN '$1000 to $2000'
ELSE 'Over $2000'
END As 'Price Range',
COUNT([Name]) As 'Product Count'
FROM [AdventureWorks2019].[Production].[Product]
GROUP BY
CASE
WHEN ListPrice = 0 THEN 'Manufacturing Item - Not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN '$50 to $250'
WHEN ListPrice >= 250 and ListPrice < 500 THEN '$250 to $500'
WHEN ListPrice >= 500 and ListPrice < 1000 THEN '$500 to $1000'
WHEN ListPrice >= 1000 and ListPrice <= 2000 THEN '$1000 to $2000'
ELSE 'Over $2000'
END
ORDER BY 'Product Count' desc 
Note that we could alternatively include the CASE expression in the GROUP BY using a subquery instead of repeating the CASE expression in the GROUP BY.
Order by a CASE Statement
Another useful example for the CASE expression is when you want to order your result set in a specific way.
The example below shows how the CASE expression can be used to order a list of employees, with the senior ones listed at the top in a specific order, followed by the others in alphabetical order.
SELECT
[LastName],
[JobTitle]
FROM [AdventureWorks2019].[HumanResources].[vEmployee]
ORDER BY
CASE
When [JobTitle] = 'Chief Executive Officer' THEN 1
When [JobTitle] = 'Chief Financial Officer' THEN 2
When [JobTitle] = 'Vice President of Sales' THEN 3
When [JobTitle] = 'Vice President of Engineering' THEN 4
When [JobTitle] = 'Vice President of Production' THEN 5
ELSE 6
END,
[JobTitle]
Summary
This article has introduced the CASE expression which is used to compare conditions and return certain values when conditions are met. It mimics the behavior of the IF (and newer IFS) function in Excel.
Use the CASE expression when you need to add contextual information to the results of your queries and also when you need to manipulate the order of result sets. There are other use cases beyond these two simple examples, but these are the most common ones.
If your organization uses both SQL Server and Excel, then check out the SQL Spreads Add-In for Excel; it will greatly simplify how you manage your data environment.
