An Introduction to SQL Case

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: 

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 result_one), then condition_two (and, if true, returning result_two), and if neither of those conditions are met, falling through to the ELSE clause and returning result_three.

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.)

PEOPLE:

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:

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() and NullIf().

Coalesce()

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()

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

Now the mailroom has everything needed to handle shipping expeditiously.

Conclusion

SQL Case provides the ability to control program flow through comparison. 

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

Start Learning

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 );