By: Tim Wiseman | Updated: 2016-12-02 | Comments (6) | Related: More > T-SQL
ProblemHow can I incorporate logic similar to an If/Else construct into a T-SQL Query?
T-SQL provides the case expression which can be used to provide a switch, similar to an if/else construct in other languages, within a query. There are two slightly different constructs for the case expression: a simple case expression which can only evaluate equality, and a searched case expression which allows for more nuanced comparisons.
Simple Case Expression
Simple case expressions take a single input expression and then compare it for equality to a series of specific values covered by when clauses. The resulting expression for each value is supplied by a then clause. Else may optionally be used to specify a resulting value for when no match was found. This is easier to see with examples. So, to look at some examples, let's start with a table that lists a few Medal of Honor recipients from the Army.
create table dbo.MedalOfHonor ( Id int identity (1,1) primary key, MilRank varchar(50), SoldierName varchar(50), YearOfAction varchar(4) ) insert into dbo.MedalOfHonor (MilRank, SoldierName, YearOfAction) values ('Staff Sergeant', 'Ty Carter', '2009'), ('Captain', 'Florent Groberg', '2012'), ('Major', 'William D. Swenson', '2009'), ('Corporal', 'Frank L. Anders', '1899'), ('First Lieutenant', 'George C. Shaw', '1903')
The table lists out the rank, but not the paygrade of the recipients. If we wanted a query to return the paygrade, we could use a case expression to correlate them. It could look like:
select MilRank, SoldierName, case MilRank when 'Staff Sergeant' then 'E-6' when 'Captain' then 'O-3' when 'Major' then 'O-4' when 'Corporal' then 'E-4' when 'First Lieutenant' then 'O-2' else 'No Rank Given' end as 'PayGrade' from dbo.MedalOfHonor
As an alternative, this use of Case can be performed by creating a table with the appropriate data and joining them rather than using the case expression. Generally, this will be the better way to go when the comparisons will be made in many separate queries, especially when there are many possibilities that would need to be listed in the case expression. Here, this would look like:
insert into dbo.RankToPayGrade (MilRank, PayGrade) values ('Staff Sergeant','E-6'), ('Captain', 'O-3'), ('Major', 'O-4'), ('Corporal', 'E-4'), ('First Lieutenant', 'O-2') select M.MilRank, M.SoldierName, P.PayGrade from dbo.MedalOfHonor M join dbo.RankToPayGrade P on M.MilRank = P.MilRank
This gives the same results as above. But the logic is not quite identical. The case expression provided a convenient else function to catch any ranks that were missing, either because the table was lacking them or because they were not listed a possibility in the case expression's list of when comparisons. We can more fully replicate the logic by using a left join and coalesce. Like:
select M.MilRank, M.SoldierName, coalesce(P.PayGrade, 'No Rank Given') as Paygrade from dbo.MedalOfHonor M left join dbo.RankToPayGrade P on M.MilRank = P.MilRank
Although this query using coalesce produces the same results as the original query with the case expression, they get there by different paths with different execution plans.
Searched Case Expression
Instead of taking a single input expression that is compared for equality, a searched case expression takes a series of expressions to evaluate for truth and returns value in the result expression for the first part that evaluates as true. For example, if we want to differentiate between officers and enlisted, it could look like:
select MilRank, SoldierName, case when MilRank in ('Captain', 'Major', 'First Lieutenant') then 'Officer' when MilRank in ('Staff Sergeant', 'Corporal') then 'Enlisted' else 'Other' end as Officer from dbo.MedalOfHonor M order by Officer
Since this version of the case expression may accept any Boolean expression instead of evaluating a strict equality, it is important to remember that it will return the result expression for the first when clause that comes up true even when more than one of them could come up true. For instance, if we want to find the century for the action the Medal of Honor was issued for:
select SoldierName, YearOfAction, case when YearOfAction > ='2001' then '21st' when YearOfAction >= '1901' then '20th' when YearOfAction >= '1801' then '19th' end as Century from dbo.MedalOfHonor order by Century desc
In that case, it provides the correct results, but only because we put the options in the right order. Captain Groberg was recognized for actions in 2012. That is clearly after 1801 and 1901 as well as 2001. The query provides the correct results only because we put the options in sequence so that it will stop looking when it sees that his year of action was on or after 2001.
Locations of Case Expressions
Case expressions can be put into a SQL statement anywhere it would accept an expression. This includes the where, order by, and having clauses and they can be used for update, delete, and merge statements just as easily as using them with a select statement. For instance, if we wanted to simplify our MilRank information to only show whether the person was an officer or enlisted, we could write an update statement like:
update dbo.MedalOfHonor set MilRank = (CASE when MilRank in ('Captain', 'Major', 'First Lieutenant') then 'Officer' when MilRank in ('Staff Sergeant', 'Corporal') then 'Enlisted' else 'Other' end) select * from dbo.MedalOfHonor
The case expression is a flexible and effective way of adding conditional logic into a SQL statement. It can often server a function similar to an If/Else construct in other languages. In many cases, if the same simple case expression will be used repeatedly or with a large number of possible matches, it may be better to use a join with a new table containing the information.
- Armando Prato provides ways to use case expression in place of dynamic SQL.
- Ken Simmons provides more details on the uses of Coalesce.
- Jeremy Kadlec provides more examples of using join.
Last Updated: 2016-12-02
About the author
View all my tips