Exploring the various uses of SQL's CASE keyword

by Arthur Fuller | Jul 21, 2005 7:00:00 AM

Tags: SQL, Arthur Fuller, IS NULL, SQL Server Newsletter

Takeaway: SQL's CASE keyword is useful in a variety of situations beyond merely selecting custom columns. Discover how you can use CASE to deliver grouped and ordered result sets that you might have thought were impossible.

The CASE keyword may be one of the most underused keywords in SQL. While you've probably used it to create a column, it has many more uses. For example, you can use CASE in a WHERE clause.

First, let's review the syntax for CASE. In a typical SELECT, it looks similar to this:

SELECT <myColumnSpec> =
CASE
WHEN <A> THEN <somethingA>
WHEN <B> THEN <somethingB>
ELSE <somethingE>
END

You would substitute specific arguments for the markers in the previous code. Here's a simple example:

USE pubs
GO
SELECT
    Title,
    'Price Range' =
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END
FROM titles
ORDER BY price
GO

This is the typical use of CASE, but you can do a lot more with CASE. For example, see how CASE is used in a GROUP BY clause:

SELECT 'Number of Titles', Count(*)
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END
GO

You can even combine these options, adding an ORDER BY clause, like so:

USE pubs
GO
SELECT
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END AS Range,
    Title
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END,
    Title
ORDER BY
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 and 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END,
    Title
GO

Note that the query above needs to repeat the CASE block in the SELECT block in order to use it in the GROUP BY block.

CASE is useful in a variety of situations beyond merely selecting custom columns. With a little forethought, you can deliver grouped and ordered result sets that you might have thought were impossible.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Print/View all Posts Comments on this article

Sql's Case Keyword ramasubramanianm@... | 08/30/05

Showing Description in Group by clause kvidyesh@... | 08/30/05

Why make it so hard and so slow? jcelko212@... | 08/30/05

re: Why make it so hard and so slow? dplaut@... | 08/30/05

Why tell people to violate Standards? jcelko212@... | 08/30/05

The article's purpose KeeBored | 08/30/05

CASE Expression and Access 2003 andeezle | 09/07/05

A little late in coming, but... RobinHood70 | 09/14/05

Can you give example? billtepe@... | 08/30/05

More Readable Form RobinHood70 | 09/02/05