T-SQL coding

A Comparison of Various T-SQL Conditional Statements

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:

  1. 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!
  2. 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!).
  3. 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.
  4. 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';

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s