I had a developer recently ask me a question about the CASE statement in T-SQL and it got me thinking about this topic…there are several different ways that one can code conditional statements in T-SQL and I don’t think that I have ever seen the different statements side-by-side…so here it goes; I hope this helps!
What I’m talking about here are the different ways that one can evaluate different conditions within a DML statement. The most common is the CASE statement, so I will cover that first. There are several other options that I will introduce here: IIF, COALESCE, CHOOSE, and NULLIF can be used in different situations to evaluate different conditions that you may encounter.
I haven’t done exhaustive testing of each of these, but during my coding/testing it appears to me that the query optimizer treats each of these basically the same as a CASE statement; there doesn’t appear to be (from a performance perspective) any advantage of one over the other; it’s mostly just a developer preference. What I’d like to do is to introduce the CASE statement and then, since it’s the most flexible, use the CASE statement and compare it to each of the other statements above showing how each of them is just a “special edition” of CASE. This will just be a quick introduction to each statement; for details on each please refer to the Books Online.
Note that each of these statements can be used anywhere within a DML statement (SELECT, INSERT, UPDATE, DELETE, and even the SET statement) where you need to evaluate a condition. That means you can use them in a SELECT column list, in a WHERE clause, in an ORDER BY or GROUP BY, to SET a value in an UPDATE, even in a JOIN (the ON clause).
Let’s start with CASE. The CASE statement is used in T-SQL to evaluate a list of conditions and return a value. It’s like coding an IF…ELSE IF…ELSE… statement; you can have as many conditions as you need, and coding is compact and readable. Here’s a quick example (I’ll use AdventureWorks2014 as my sample database):
SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber;
Here are some observations about CASE:
- Evaluations are performed in sequence and evaluation stops as soon as the first true statement is reached; the order of your WHEN clauses is important!
- The datatype of the return value is based on the precedence order in SQL Server; see the Books Online for those details (I try to make sure that all of my return values are the same datatype!).
- The ELSE clause is optional, but if you don’t have one then you will get a null value returned if all conditions are false.
- Each WHEN clause can have different conditions and those conditions can include AND/OR logic.
Let’s move on to the IIF statement. This statement (new to SQL Server 2012) evaluates a single expression and returns one of two values depending on whether the evaluation is true or false. The IIF statement can only evaluate a single condition and can only return based on true/false. Here is an example. These two SELECT statements produce the same result; one using CASE and the second using IIF:
SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice < 50 THEN 'Under $50' ELSE '$50 or Over' END FROM Production.Product ORDER BY ProductNumber; SELECT ProductNumber, Name, "Price Range" = IIF (ListPrice < 50, 'Under $50', '$50 or Over') FROM Production.Product ORDER BY ProductNumber;
The CHOOSE statement is also new to SQL Server 2012. It returns the nth item specified based on an index. Again, it’s a special kind of a CASE statement, but one that you might find easier to use based on your coding preferences. I prefer to put these kind of lists into tables, but you may find a use for this. Here is an example:
SELECT ProductCategoryID, CASE WHEN ProductCategoryID = 1 then 'A - Bikes' WHEN ProductCategoryID = 2 then 'B - Components' WHEN ProductCategoryID = 3 then 'C - Clothing' WHEN ProductCategoryID = 4 then 'D - Accessories' ELSE 'E - other' END FROM Production.ProductCategory; SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A - Bikes','B - Components','C - Clothing','D - Accessories','E - other') FROM Production.ProductCategory;
Next we have the COALESCE statement. You will find more details on this statement in my blog entry from November 9, 2016, Various Uses for Coalesce. This statement is a “shortcut” for a CASE statement when you need to check for nulls, and multiple null checks can be evaluated in a single statement (returning one result, the first non-null value in the list). Again, here is a simple example:
SELECT ProductNumber, Name, CASE WHEN Color is null then 'No Color' ELSE Color END FROM Production.Product ORDER BY ProductNumber; SELECT ProductNumber, Name, COALESCE( Color, 'No Color') FROM Production.Product ORDER BY ProductNumber;
Last we have the NULLIF statement. I will admit I haven’t found a use for this…yet. This statement accepts 2 expressions and returns a NULL if both are equal. If not equal it returns the value of the first expression. Quick example:
SELECT BusinessEntityID, OrganizationLevel, CASE WHEN VacationHours = 0 then null ELSE VacationHours END as VacTaken FROM HumanResources.Employee WHERE hiredate > '1/1/2009'; SELECT BusinessEntityID, OrganizationLevel, NULLIF(VacationHours, 0) as VacTaken FROM HumanResources.Employee WHERE hiredate > '1/1/2009';