SQL CASE Statement in WHERE Clause Examples

By: Rajendra Gupta | Updated: 2023-06-26 | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | > TSQL

Problem

The WHERE clause in SQL queries filters data based on a specified expression or condition. We usually define a static condition or value, however, if you need to filter data based on different conditions, this can be done using SQL CASE in the WHERE clause.

Solution

This SQL tutorial will guide you on conditionally filtering using the WHERE clause using a SQL CASE statement.

Introduction to SQL CASE Statement

The SQL CASE statement specifies a conditional expression to perform different actions depending on the input expression value. The syntax for the CASE statement in a SQL database is:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    WHEN valueN THEN resultN
    ELSE defaultValue
END

For example, in the AdventureWorks2019 sample database, look at the [MaritalStatus] column value from the [HumanResources].[Employee] table. It contains the abbreviation "S" for Single and "M" for Married.

SELECT TOP (5) 
       [NationalIDNumber]
      ,[LoginID]
      ,[MaritalStatus]
FROM [AdventureWorks2019].[HumanResources].[Employee]

We can use a CASE statement to replace the abbreviation with the custom text, as shown below. It changes the abbreviations "S" and "M" to "Single" and "Married", respectively.

SELECT TOP (5) 
       [NationalIDNumber]
      ,[LoginID]
      ,CASE [MaritalStatus]
          WHEN 'S' THEN 'Single'
          WHEN 'M' THEN 'Married'
          ELSE 'Unknown'
          END as [MaritalStatus]
FROM [AdventureWorks2019].[HumanResources].[Employee]

We can use CASE in SQL statements such as SELECT, WHERE, and ORDER BY.

CASE Statement in the WHERE Clause

The CASE statement in the WHERE clause can conditionally filter rows based on defined criteria. The syntax for the CASE statement in the WHERE clause is shown below. The CASE expression matches the condition and returns the value of the first THEN clause. If none of the conditions are true, it returns the value of the ELSE clause.

SELECT Col1,Col2….ColN
FROM ...
WHERE CASE
         WHEN condition1 THEN value1
         WHEN condition2 THEN value2
         ...
         ELSE valueN
      END

Let’s see it used in a few examples.

The following query uses the CASE statement in the WHERE clause to filter single employees.

-- source: https://www.MSSQLTips.com

SELECT NationalIDNumber,MaritalStatus 
FROM [AdventureWorks2019].[HumanResources].[Employee]
WHERE CASE
         WHEN [MaritalStatus] = 'S' Then 1
         ELSE 0
      END = 1;

As shown below, it returns only rows where [MaritialStatus] is S.

In another example, the CASE statement in the WHERE clause consists of multiple WHEN conditions. SQL Server evaluates the first condition and checks for records satisfying the given conditions.

-- source: https://www.MSSQLTips.com

SELECT SalesOrderID,TerritoryID,ShipMethodID,BillToAddressID
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE CASE
         WHEN [TerritoryID] = 5 AND [ShipMethodID] = 5 THEN 1
         WHEN BillToAddressID = 947 THEN 1
         ELSE 0
      END = 1;

Here is the output of the CASE statement conditions.

If we want to exclude the records instead of including them, we can change the filter to return records with values set to 0 by the CASE statement.

-- source: https://www.MSSQLTips.com

SELECT SalesOrderID,TerritoryID,ShipMethodID,BillToAddressID
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE CASE
         WHEN [TerritoryID] = 5 AND [ShipMethodID] = 5 THEN 1
         WHEN BillToAddressID = 947 THEN 1
         ELSE 0
      END = 0;

Similarly, you can combine multiple CASE statement conditions with OR and AND operators. For example, the query below returns both Single and Married employees. You can see we combined two CASE statements with the OR operator.

-- source: https://www.MSSQLTips.com

SELECT NationalIDNumber,MaritalStatus 
FROM [AdventureWorks2019].[HumanResources].[Employee]
WHERE 
   (CASE
       WHEN [MaritalStatus] = 'S' THEN 1
       ELSE 0
    END = 1)
 OR 
   (CASE
       WHEN [MaritalStatus] = 'M' THEN 1
       ELSE 0
    END = 1);

We can also use dynamic values or calculate values in the CASE statement in the WHERE condition. For example, suppose we want records with an average total due amount over 3000.

-- source: https://www.MSSQLTips.com

SELECT *
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE TotalDue >
    CASE
        WHEN (SELECT AVG(TotalDue) FROM [Sales].[SalesOrderHeader] ) > 3000 THEN 1
        ELSE 0
    END = 1;

Let’s look at another example where the CASE statement filters records when the OrderDate is between specified dates.

-- source: https://www.MSSQLTips.com

SELECT *
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE CASE
          WHEN OrderDate BETWEEN '2023-01-01' AND '2023-01-31' THEN 1
          ELSE 0
      END = 1;

Note: all of these queries could be written without the CASE, but sometimes using CASE makes it easier to read the code. Here is an example of using CASE versus using OR for the query. These both return the same results and also have the same execution plan.

-- source: https://www.MSSQLTips.com

SELECT SalesOrderID,TerritoryID,ShipMethodID,BillToAddressID
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE CASE
         WHEN [TerritoryID] = 5 AND [ShipMethodID] = 5 THEN 1
         WHEN BillToAddressID = 947 THEN 1
         ELSE 0
      END = 1;

SELECT SalesOrderID, TerritoryID, ShipMethodID, BillToAddressID
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE ([TerritoryID] = 5 AND [ShipMethodID] = 5) OR BillToAddressID = 947;

Here is another example of using CASE.

-- source: https://www.MSSQLTips.com

SELECT
   bom.BillOfMaterialsID,
   bom.ProductAssemblyID,
   u.Name,
   u.UnitMeasureCode,
   bom.PerAssemblyQty
FROM Production.BillOfMaterials bom
INNER JOIN Production.UnitMeasure u on u.UnitMeasureCode = bom.UnitMeasureCode
WHERE
   PerAssemblyQty >= CASE u.UnitMeasureCode
                       WHEN 'EA' THEN 30
                       WHEN 'OZ' THEN 9
                       WHEN 'IN' THEN 40
                       ELSE 0
                   END;

Nested CASE Statement in the WHERE Clause

You can combine multiple conditions with a nested CASE statement in the WHERE clause. For example, we want records from the [SalesOrderHeader] table where the orderdate is between specified dates. If this condition is satisfied, check for orders with a value 1 for column [OnlineOrderFlag]:

-- source: https://www.MSSQLTips.com

SELECT *
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader]
WHERE CASE
          WHEN OrderDate BETWEEN '2023-01-01' AND '2023-12-31' THEN
          CASE
              WHEN OnlineOrderFlag = 1 THEN 1
              ELSE 0
          END
          ELSE 0
      END = 1;

CASE Statement in WHERE Clause as SubQuery

We can use CASE statements in a subquery as well. Here is an example SELECT statement:

-- source: https://www.MSSQLTips.com

SELECT SalesOrderID,SalesOrderNumber,TotalDue,AccountNumber
FROM [AdventureWorks2019].[Sales].[SalesOrderHeader] 
WHERE SalesOrderID IN (
          SELECT SalesOrderID
          FROM [AdventureWorks2019].[Sales].[SalesOrderHeader] 
          WHERE CASE 
                    WHEN TotalDue > 1000 THEN 1
                    ELSE 0
                END = 1
      );

CASE Statement in WHERE Clause as SubQuery

Next Steps

Explore Microsoft docs for CASE statement documentation.
Read more T-SQL related articles

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-06-26

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression