Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.

-
DAYS
-
HOURS
-
MINUTES
-
SECONDS

Free Beginners SQL Course!

SQL CASE Statement – Simple and Searched Forms

·

·

The main purpose of a SQL CASE expression returns a value based on one or more conditional tests.  Use CASE expressions anywhere in a SQL statement expression is allowed. Though truly an expression, some people refer to them as “CASE statements.”  This most likely stems from their use in programming languages.

The SQL CASE expression is extremely versatile and used throughout SQLServer queries.  In particular it is used in the SELECT column list, GROUP BY, HAVING, and ORDER BY clauses. The CASE expression also standardizes (beautify) data or performs checks to protect against errors, such as divide by zero.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database.   Getting Started Using SQL Server using my free guide and free Microsoft tools.

SQL Server CASE Statement Forms

There are two forms for the CASE clause:  simple and searched.  Both forms return a result based on testing an expression. Though technically expressions, you’ll see many people refer to it as a statement.

The simple SQL CASE statement is used for equality tests.  It tests one expression against multiple values, this makes it great for transforming one set of values, such as abbreviations to their corresponding long form.

The searched SQL CASE statement uses a more comprehensive expression evaluation format.  It is good when you wish to work with ranges of data, such as salary ranges or ages.

We first start out with the simple form, and then cover searched.

CASE expression Simple Form

The simple form of the CASE expression compares the results of an expression with a series of tests and return a “result” when the “test” returns true.

The general form for a simple form CASE expression is:

CASE expression
WHEN test THEN result

ELSE otherResult
END

The ELSE statement is optional in a CASE expression.  It returns “otherResult” when no matches are made and ELSE is present. If there is no ELSE in the CASE statement, then it returns NULL.

The ELSE clause is a great way to catch bad or unexpected data values, and return a result other than NULL.

Here are some things to consider when using the simple CASE expression:

  • Allows only equality comparisons.
  • Evaluates tests are evaluated in the order defined.
  • Returns the result corresponding to the first TRUE test.
  • If no match is made, case returns NULL unless ELSE is present.

CASE expression Searched Form

The search form of the CASE expression allows for more versatile testing.  Use it to evaluate a greater range of tests.  In fact, any Boolean expression qualifies as a test.

A searched from CASE expression has this format

CASE
WHEN booleanExpression THEN result

ELSE otherResult
END

With the searched form, use WHEN clauses to evaluate Boolean expressions.  The result of the first TRUE Boolean expression is returned.

Below is the searched CASE form of the employee gender example  from the previous section.

SELECT JobTitle,
CASE
WHEN Gender = 'M' THEN 'Male'
WHEN Gender = 'F' THEN 'Female'
ELSE 'Unknown Value'
END
FROM HumanResources.Employee

We also used this same example for the simple SQL case statement.  I did this so you could see the subtle difference.  Notice that each WHEN clause now contains the test as a Boolean expression.

Comparison of Simple and Searched Forms SQL CASE

Here are the statements side-by-side:

Simple SQL CASE Expression versus CASE SQL Searched expression.
Simple versus Searched CASE expression Simple versus Searched CASE expression

I tend to use the searched CASE expression format in all my SQL.  This reason is simple, I only have to remember one format!

Since we’re testing Boolean expressions, the searched CASE statement isn’t limited to just equality tests. 

Use CASE to Compare a Range of Values

This makes this form really good for comparing ranges of values.   Perhaps the sales manager of Adventure Works wants to organize products by price range.  How could this be done with SQL?

Given the following names and ranges provided by the sales manager, we can construct  a CASE expression to compare the ListPrice to a range of values and then return the appropriate price range name.

Price Range Definitions for CASE SQL Example.

The case statement is placed in the SELECT column list and returns a character value.  Here’s the SQL that does the trick:

SELECT Name,
ListPrice,
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange
FROM Production.Product

When you run this query you’ll see PriceRange listed and displaying values according to the ranges specified in the CASE expression:

Price Range Query Results
CASE Statement Results

Data Transformation Example

There are several reasons to use a CASE statement.  The first is to transform data from one set of values to another.  For instance, to display an employee’s gender as “Male” or “Female,” when your data is encoded as “M” or “F,” use a CASE expression to test for the single character representation and return its corresponding long form.

The example for this is:

SELECT JobTitle,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Unknown Value'
END
FROM HumanResources.Employee

Data Standardization Example

Similarly you can use a simple CASE clause to standardize several values into one.  Extending our example maps several variations to either Male or Female:

SELECT JobTitle,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN '0' THEN 'Male'
WHEN 'F' THEN 'Female'
WHEN '1' THEN 'Female'
ELSE 'Unknown Value'
END
FROM HumanResources.Employee

You may be wondering if you could just create another table in your database and use that to lookup the values.  I would tend to agree that would be the best, but in many situations you won’t have permission to create tables in the database.  In this case you’re left to your wits a SELECT statement’s provides.

SQL CASE Prevents SQL Errors!

CASE statements can also be used to help prevent errors.   A good example is to test for valid values within expressions such as when you divide numbers.

Consider

SELECT ProductID,
Name,
ProductNumber,
StandardCost,
ListPrice,
StandardCost / ListPrice as CostOfGoodSold
FROM Production.Product

This statement return the message

Divide by zero error encountered.

By using a CASE clause we can ensure we don’t inadvertently divide by zero.

SELECT ProductID,
       Name,
       ProductNumber,
       StandardCost,
       ListPrice,
       CASE
          WHEN ListPrice = 0 Then NULL
          ELSE StandardCost / ListPrice
       END as CostOfGoodSold
FROM   Production.Product

A CASE expression can be used wherever an expression can be used.  This means you can use it to return a column value result or even use it in an ORDER BY clause.

CASE in SELECT

In the following section we’ll explore using CASE in the ORDER BY and GROUP BY clauses.

CASE expression in ORDER BY

Continuing on with the sales manager request, suppose she also wants to see the products sorted by price range and then product name.  We’ve seen how we can display the price ranges as a column, but how do we sort?

Actually it is pretty easy.  Since CASE is an expression, we can use it as once of the values from which order the results.  Remember, we aren’t limited to just sorting table columns, we can also sort an expression.

Here is the query to sort by the price range.

SELECT   Name,
ListPrice
FROM     Production.Product
ORDER BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END,
Name

We can then add CASE statement to SELECT list to also display the price range.

SELECT   Name,
ListPrice,
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange
FROM     Production.Product
ORDER BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END,
Name

As you can see, things start to get complicated.  Do you see how the CASE statement is repeated in both the SELECT list and ORDER BY?  Fortunately, we can simplify this a bit, but removing the CASE statement from the ORDER BY and replacing it with the SELECT list CASE expression’s alias name PriceRange as so:

 SELECT   Name,
ListPrice,
CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange
FROM     Production.Product
ORDER BY PriceRange, Name

CASE expression in GROUP BY

Now that we’ve given the sales manager a detailed listing she wants to see summary data – doesn’t it ever end?  In my experience it doesn’t, so knowing lots of SQL to satiate customer demands is your key to success.

Anyways, the good news is we can use the CASE expression we’ve built to create summary groups.  In the following SQL we’re grouping the data by PriceRange.  Summary statistics on the minimum, maximum, and average ListPrice are created.

SELECT
  CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END as PriceRange,
Min(ListPrice) as MinPrice,
Max(ListPrice) as MaxPrice,
AVG(ListPrice) as AvgPrice,
Count(ListPrice) as NumberOfProducts
FROM     Production.Product
GROUP BY CASE
WHEN ListPrice = 0 THEN 'No Price'
WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
WHEN ListPrice > 2000 THEN 'Luxury'
ELSE 'UNLISTED'
END
ORDER BY MinPrice

Unlike the ORDER BY clause, we can’t reference the column alias PriceRange in the GROUP BY.  The entire CASE expression has to be repeated.  Here are the results of our query:

Results of SQL CASE expression in GROUP BY
Results – CASE Expression in GROUP BY

CASE in WHERE Clause

You can also use a CASE in the WHERE clause. This is particularly handy if want one of several values to evaluate to a condition.

Continuing with the Gender example, we can write the following CASE in a WHERE clause to test for female employees:

SELECT JobTitle, Gender
FROM   HumanResources.Employee
WHERE  CASE Gender
          WHEN 'M' THEN 'Male'
          WHEN '0' THEN 'Male'
          WHEN 'F' THEN 'Female'
          WHEN '1' THEN 'Female'
          ELSE 'Unknown Value'
        END = 'Female' 
Using SQL CASE in WHERE Clause

You could have also solved this problem using BOOLEAN logic, or a IN clause, but as the logic get more complex, you may find it is easier to express it with a CASE, rather than those other techniques. It can make your code easier to read and maintain.

CASE with CTE

Speaking of readability and maintainability, why not just wrap the CASE expression within a Common Table Express (CTE) to avoid “sprinkling” the CASE logic through out your code?

Here we use a CTE to define the CASE result once, then reuse that result in the outer query:

;WITH cteEmployee as
(
   SELECT JobTitle, Gender,
      CASE Gender
         WHEN 'M' THEN 'Male'
         WHEN '0' THEN 'Male'
         WHEN 'F' THEN 'Female'
         WHEN '1' THEN 'Female'
         ELSE 'Unknown Value'
      END GenderResult
FROM HumanResources.Employee
)
SELECT JobTitle, Gender, GenderResult
FROM cteEmployee
WHERE GenderResult = 'Female'
ORDER BY GenderResult

To make it more clear, check out the following which shows how this is done with a CASE statement. Notice the cteEmployee is used to calculate the GenderResult, which is then reused in the SELECT, WHERE, and ORDER BY clauses!

Using a CASE clause within CTE (Common Table Expression)
Using a CASE clause within CTE

To learn more about Common Table Expressions, check out Common Table Expressions – The Ultimate Guide.

Wrap Up

As you can see, using CASE expressions adds versatility to your SQL statements.  They not only allow you to transform data from one set of values to another, but can also be used to ensure statements don’t return errors.

Out of the two forms, simple and searched, I tend use the search form.  The reason is that the simple form is limited to equality tests; whereas, the searched form can do that and more.

Since CASE expressions are expressions and not statements or clauses, they can be used where any other expression is used.  That mean you can use throughout the SELECT statement and elsewhere in SQL.

18 responses to “SQL CASE Statement – Simple and Searched Forms”
  1. Jeannie

    can you use case more than once in an expression?
    I have a query tha calculates the cost of insurence.
    but i need to see if i can add a *Case statement to my query. like so —–(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance

    SELECT
    'Cost of Insurance' AS strType,
    ISNULL(SUM(CASE WHEN dtReport = @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intDT,
    ISNULL(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfMonth AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intMTD,
    ISNUll(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfQuarter AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intQTD,
    IsNull(SUM(CASE WHEN dtReport BETWEEN tblDate.dtFirstDayOfYear AND @EndDate THEN ROUND(mnyInsurance,2) ELSE 0 END),0) AS intYTD,
    —–(SUM(ROUND(mnyInsurance,2)*CASE WHEN decInsuranceShare IS NOT NULL THEN 1-decInsuranceShare ELSE 0.60 END),0) Insurance

    3 as intOrder
    FROM
    tblUBMReport WITH (NOLOCK)
    LEFT JOIN tblUBMInsuranceShare ON tblUBMInsuranceShare.intProp = tblUBMReport.intProp
    INNER JOIN tblDate WITH (NOLOCK) ON tblDate.dtDate = @EndDate
    WHERE
    intProp IN (@Props)

  2. Savy

    Hi My question is Create a price segmentation for products by defining a criteria that places each item in a segment as
    follows: If price is less than £200 then it’s low value. If price is between £201 and £750 then it is midvalue. If price is between £751 and £1250 then it is mid to high value. All else is higher value. Filter the results only for products that are black, silver and red (colour).
    i have derived as below but how can i add where clause for Color if i add WHERE clause it shows error

    SELECT
    [ProductID]
    ,[Name]
    ,Color
    ,[ListPrice]

    ,CASE
    WHEN [ListPrice]<= 200 THEN 'Low Value'
    WHEN [ListPrice] BETWEEN 201 AND 750 THEN 'Mid Value'
    WHEN [ListPrice] BETWEEN £751 AND £1250 THEN 'High Value'

    ELSE 'Higher Value'

    END AS ProductSegment

    FROM [Production].[Product]

    Please help

  3. Chandni

    If we had to create a case statment for a column we created similar to a nonstatic column in the table. EX: If CostOfGoodSold is greater then 500 then good otherwise not good, how would you write that

    SELECT ProductID,
    Name,
    ProductNumber,
    StandardCost,
    ListPrice,
    CASE
    WHEN ListPrice = 0 Then NULL
    ELSE StandardCost / ListPrice
    END as CostOfGoodSold
    FROM Production.Product

  4. Book

    about getting a min of a case example

    SELECT CASE WHEN pr_off=0 THEN price
    ELSE pr_off END AS ‘FinalPrice’, Ident, po from table1
    where order_qty>0 and (pr_off>0 or price>0) and Ident = Ident and po!=”Test” and po like TEST%’ and `Status`!=’cancelled’ GROUP BY Ident ORDER BY
    Ident, po

    this query gives me results that are very close to correct but does not give me the lowest FinalPrice for each Ident it gives me the FinalPrice of the First ident in the table–thoughts on how to fix this

  5. Vivek

    Thanks for an awesome post! :)

    1. Kris Wenzel

      My pleasure!

  6. ffff

  7. Anh Nguyen

    Hi Kris, I want to have some practice problem on CASE expression. I would love if you can add some practices or if you could recommend some sources so I can solve some problems. Thank you so much for this beautiful tutorial!

  8. AMIT JAIN

    can you use like in case statement searched form ? using like and regex will make it even more powerful, if we can do it then could you please provide some examples

  9. Kath

    Hi Kris –

    How can I change the color font using a CASE statement? I would like to do a measure of a-b and if the result is <0.2 then highlight it or change the text font to red. How can I do this in a case statment ?

    1. Kris Wenzel

      This wouldn’t apply to SQL, as SQL CASE is working with the data. To work with the colors, you would need to work with your user interface tool, such as HTML or CSS.

      I suppose you could embed HTML into your SQL output, but then, again, that data would have to be “pumped” into your UI for processing.

  10. Alex Kuligowski

    Hey Kris – sweet article. Super helpful. Here’s a friendly poke to review the last sentence of your bio.

    Kris Wenzel

    Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA.He has a BSE in Computer Engineering from the University of Michigan and an MBA from the University of Notre Dame.Kris has written hundreds of blog articles and many online courses. He is loves helping others learn SQL.

    Cheers!

  11. Indu

    Can i use case in where clause??

  12. BT

    can we use “IN” clause in “Then” of case when statement ?If yes then how?

    1. Hi, Think of IN as a comparison operator. It is used in the WHERE clause. Given this, you could write a SELECT statement within the CASE WHEN, which uses the IN clause.

  13. Karthik

    Can we use Case in FORM clause?

  14. jamie

    In this example,

    SELECT Name,
    ListPrice,
    CASE
    WHEN ListPrice = 0 THEN 'No Price'
    WHEN ListPrice > 0 AND ListPrice 50 AND ListPrice 150 AND ListPrice 500 AND ListPrice 1000 AND ListPrice 2000 THEN 'Luxury'
    ELSE 'UNLISTED'
    END as PriceRange
    FROM Production.Product
    ORDER BY PriceRange,
    Name

    ordering by PriceRange would sort alphabetically wouldn’t it? That’s not what the sales manager wanted.

    1. Yes, as it is written it will sort alphabetically. To help the sales manager get the ranges in order of magnitude, you could put a number in front of the range., such as ‘6 – Luxury.’

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog


MySQL PostgreSQL SQLite SQL Server