The key to unlocking the power of computer programming is conditional flow control, the if-then-else constructs which cause the program to perform separate actions depending upon the evaluation of some Boolean (true or false) assertion. SQL has its own conditional flow control; the SQL Case statement.
The mindful construction of conditional tests is important to get the expected results:
|a real-world conditional test|
|A programmer gets dispatched to the grocery store with the instructions “get a loaf of bread and if they have eggs get a dozen.” Upon returning home with a dozen loaves they declare “they had eggs.”|
Syntax of SQL Case
SQL Case has a flexible syntax. Its general form provides for a number of test conditions and the corresponding result. The test conditions are what would be expected in a SQL Select WHERE clause. The optional ELSE portion allows a result to be specified if none of the above conditions have been met; without an ELSE clause, a NULL is returned.
CASE WHEN condition_one THEN result_one WHEN condition_two THEN result_two ELSE result_three END;
The SQL Case statement is processed from the top, evaluating
condition_one first (and, if true, returning
condition_two (and, if true, returning
result_two), and if neither of those conditions are met, falling through to the ELSE clause and returning
A Sample Table for SQL Case
This table describes people, their locations, and the weight of their orders. (The SQL code to generate this table is presented at the end of this post. Run all the SQL code yourself at SQL Fiddle.)
|Clarice||New York City||USA||25|
Using SQL Case to Ship a Package
SQL Case comes in two “flavors”:
- The “searched case” syntax, which allows for various boolean operators (like “less than” or “greater than”)
- The “simple case” syntax, which always uses the “is equals to” operator
SQL Case – Searched Case Syntax
We can use the equality test operator, =, to determine how to ship a package to each of our people by making a decision based upon the destination country.
SELECT NAME, CITY, COUNTRY CASE WHEN COUNTRY = 'USA' THEN 'USPS' WHEN COUNTRY = 'UK' THEN 'Royal Mail' ELSE 'other international shipper' END AS ShippingMethod FROM People ORDER BY ShippingMethod ;
Running this bit of business logic, especially with the ORDER BY clause to group shippers together, results in actionable directions for the mailroom:
|Clarice||New York City||USA||USPS|
|Geraldo||Buenos Aires||Argentina||Other international shipper|
SQL Case – Simple Case Syntax
SQL Case also provides a simpler syntax when a common value is being tested. The above code can be written in a more easy-to-read, maintainable manner.
SELECT NAME, CITY, COUNTRY CASE COUNTRY WHEN 'USA' THEN 'USPS' WHEN 'UK' THEN 'Royal Mail' ELSE 'other international shipper' END AS ShippingMethod FROM People ORDER BY ShippingMethod ;
As the simple case syntax uses the equality operator it can’t deal with NULL values (technically because NULL = NULL isn’t “true” but rather “unknown”). Welcome to three-valued logic. Instead, SQL provides two functions:
Coalesce() takes any number of arguments and returns the first non-NULL one, or NULL if all arguments are NULL. So the call
COALESCE(a, b, c) is equivalent to:
CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b WHEN c IS NOT NULL THEN c ELSE NULL END
Nullif() takes exactly two arguments. If both are NULL then NULL is returned, otherwise the first argument is returned. The call
NULLIF(value, 0) is used to prevent fatal divide-by-zero errors (because divide-by-NULL is handled differently.
Sorting Values Into Intervals with SQL Case
Let’s make the workflow in the mailroom even more streamlined by sharing what type of packaging will be needed. Adding greater-than,
>, and less-than,
<, conditional tests to the equality operator,
=, will separate the packages into intervals that correspond to the shipping requirements for WEIGHT.
Intervals, in this case, is the mathematical term that denotes sorting into groups with adjacent values; think “small,” “medium,” and “large” – useful groupings into which you can sort clothes without overlap.
SELECT NAME, CITY, COUNTRY, WEIGHT CASE COUNTRY WHEN 'USA' THEN 'USPS' WHEN 'UK' THEN 'Royal Mail' ELSE 'other international shipper' END AS Shipper, CASE WHEN WEIGHT > 0 AND WEIGHT <= 10 THEN 'small' WHEN WEIGHT > 10 AND WEIGHT <= 50 THEN 'medium' ELSE 'large' END AS BoxToUse FROM People ORDER BY Shipper, BoxToUse;
which results in
|Clarice||New York City||USA||25||USPS||medium|
|Geraldo||Buenos Aires||Argentina||37||other international shipper||medium|
Now the mailroom has everything needed to handle shipping expeditiously.
SQL Case provides the ability to control program flow through comparison.
|another real-world conditional test|
|A programmer’s partner says “while you’re at the grocery store buy some eggs.” The programmer stays home.|
SQL Case Sample Table Code
The following SQL will build and populate the sample table used in this article.
CREATE TABLE People ( NAME VARCHAR(33), CITY VARCHAR(33), COUNTRY VARCHAR(33), WEIGHT INT ) ; INSERT INTO People VALUES ( 'Alice', 'Cambridge', 'USA', 1 ), ( 'Bob', 'Cornwall', 'UK', 13 ), ( 'Clarice', 'New York City', 'USA', 25 ), ( 'David', 'Key West', 'USA', 12 ), ( 'Elizabeth', 'Jacksonville', 'USA', 55 ), ( 'Mobeen', 'Brixton', 'UK', 100 ), ( 'Geraldo', 'Buenos Aires', 'Argentina', 37 );