If you are using SQL, you are no doubt familiar with SELECT, INSERT, UPDATE, and DELETE, as well as a few functions that are useful. I've found that not many people are making active use of the CASE statement, although it is one of the most useful SQL keywords in existence. Typically a web application developer will use this type of conditional logic in his page code (using PHP, ColdFusion, or another language) making the business logic part of the presentation of the page. It is more efficient and better programming practice to put this logic in the database using a stored procedure or in the SQL statement so that the presentation of the data can be as clean and unencumbered by business logic as possible.

This article will show a few simple uses of the statement and how you can use it to simply web applications. These examples should work in SQL Server or MySQL. I will be using the Northwind database that comes free with SQL Server. Anyone using mySQL can download a Northwind creation script at my site.

If you have not read my article on SQL ordering tricks, there are several interesting examples of CASE in that article as well.

Using CASE

The CASE Statement in SQL is similar to the case statements in other programming languages. You use it when you want to divert a program in one direction or another. In SQL, you will typically use it when you want the data diverted one way or another. Let's look at the simplest form:

SELECT ContactName,
CASE WHEN Country = 'USA' THEN 'Domestic' ELSE 'Foreign' END AS CustomerType
FROM Customers

In this case, we are using it to look at the Country field, which will contain a country name. Rather than pass back the country name, we only want to know whether the customer is a domestic or foreign customer. The results will look something like this:

The CASE statement takes the place of a field in the SELECT statement. Because of that, we give the field an alias -- in this case CustomerType. This allows us to use the results of the CASE statement as if it were a field. The statement is saying "If the country is USA, return 'Domestic', else return 'Foreign'". The statement could also have been written like this, with the alias first:

SELECT ContactName,
CustomerType = CASE WHEN Country = 'USA' THEN 'Domestic' ELSE 'Foreign' END
FROM Customers

Notice the CASE statement is all on one line, which is acceptable, but writing like this makes it easier to see what is going on:

SELECT ContactName,
CustomerType =
CASE
  WHEN Country = 'USA' THEN 'Domestic'
  ELSE 'Foreign'
END
FROM Customers

A different form of the statement is to write it like this:

SELECT ContactName,
CASE Country
   WHEN 'USA' THEN 'Domestic'
   ELSE 'Foreign'
END AS CustomerType
FROM Customers

Similarly, the statement could have been written like this:

SELECT ContactName,
CustomerType =
CASE Country
   WHEN 'USA' THEN 'Domestic'
   ELSE 'Foreign'
END
FROM Customers

Typically in a web application, you might have had logic like this instead:

<cfquery name="rs" datasource="#mydsn#">
SELECT ContactName, Country FROM Customers
</cfquery>

<table>
<cfoutput query="rs">
<tr>
<td>#rs.ContactName#</td>
<td><cfif Country EQ 'USA'>Domestic<cfelse>Foreign</cfif></td>
</tr>
</cfoutput>
</table>

In PHP, it might have looked like this:

<?php
mysql_select_db($database_testconnection, $testconnection);
$query_rs = "SELECT ContactName, Country FROM Customers";
$rs = mysql_query($query_rs, $testconnection) or die(mysql_error());
$row_rs = mysql_fetch_assoc($rs);
$totalRows_rs = mysql_num_rows($rs);
?>
<table>
<?php do { ?>
<tr>
<td><?php echo $row_rs['ContactName']; ?></td>
<td><?php if ($row_rs['Country'] == 'USA') {
  echo("Domestic");
}else{
  echo("Foreign");
} ?></td>
<?php } while ($row_rs = mysql_fetch_assoc($rs)); ?>
</table>

The logic in spaghetti-coded into the presentation of the data, making the display more cumbersome than it needs to be. Using CASE, you are simply returning fields to the page and displaying them without any page-level conditional logic. If this logic had included more than two possible values, the logic would have gotten much more complex.

All of these options are "either this or this", however CASE statements are much more powerful when used with more than two options:

SELECT ContactName,
CASE Country
   WHEN 'USA' THEN 'Domestic'
   WHEN 'Canada' THEN 'Northern Neighbor'
   ELSE 'Foreign'
END AS CustomerType
FROM Customers

This gives us three options, however it could have used many more than three by simply supplying more WHEN clauses in the statement. This statement, like the previous statement, could have been written several different ways. It's a matter of personal preference how to write these statements.

SELECT ContactName,
CustomerType =
CASE Country
  WHEN 'USA' THEN 'Domestic'
  WHEN 'Canada' THEN 'Northern Neighbor'
  ELSE 'Foreign'
END
FROM Customers

You'll notice that I'm splitting my statement using logical programming syntax/tabbing. When writing SQL statements you should always obey the same types of programming style that you would use when writing your client-side or server-side code. Making the statement as readable as possible makes it easier to debug and easier to maintain. My article on readable SQL is a good starting point for learning how to maintain coding standards.

Using CASE you don't have to restrict yourself to equality statements -- and valid SQL can be used, such as greater than, less than, IN, BETWEEN, or other SQL statements:

SELECT ContactName,
CustomerType =
CASE WHEN Country IN ('USA','Mexico','Canada')
  THEN 'North America'
  ELSE 'Rest of the world'
END
FROM Customers

Using CASE you can also create more complex business logic. Here I'm using a GROUP BY and a SUM to total the orders coming in. I'm using the CASE statement to determine whether it's a large, medium or small order, and also to show the customer service person responsible for the order based on the size of the order:

SELECT SUM(UnitPrice) as TotalOrder,
Size = CASE
  WHEN SUM(UnitPrice) < 100 THEN 'Small'
  WHEN SUM(UnitPrice) BETWEEN 100 AND 300 THEN 'Medium'
  ELSE 'Large'
END,
CustomerServicePerson = CASE
  WHEN SUM(UnitPrice) < 100 THEN 'Jim'
  WHEN SUM(UnitPrice) BETWEEN 100 AND 300 THEN 'Jack'
  ELSE 'Sharon'
END
FROM [Order Details]
GROUP BY OrderID
ORDER BY SUM(UnitPrice)

Conclusion

This article has shown a brief introduction to using the CASE statement. You should be using CASE statements in your SQL rather than in your web programming language to return faster results and keep the complex business logic out of the page.