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

By:   |   Updated: 2023-06-26   |   Comments (3)   |   Related: More > 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]
CASE Statement

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]
CASE statement

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.

CASE Statement in the WHERE clause

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.

CASE Statement in the WHERE clause

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;
CASE Statement in the WHERE clause

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;
CASE Statement in the WHERE clause

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

View all my tips


Article Last Updated: 2023-06-26

Comments For This Article




Wednesday, July 19, 2023 - 8:29:33 PM - Dave Back To Top (91410)
I assume this methodology is totally un SARGEable and should only be used when we already will not or (for whatever reason) cannot use the index?

Tuesday, June 27, 2023 - 11:56:44 AM - Jeff Moden Back To Top (91347)
I may be missing it in my quick read but I don't see anywhere in the article about how this may affect performance when it comes to SARGability. Any information in that area?

Monday, June 26, 2023 - 5:17:04 PM - Doug Bishop Back To Top (91344)
Good tutorial for beginners, except for the terminology.

SELECT, INSERT, UPDATE, DELETE, SET are STATEMENTS.
WHERE is a clause.
CASE is an expression.

You can't write:
CASE WHEN X = 1 THEN UPDATE ...
WHEN X = 2 THEN INSERT ...
ELSE DELETE ...
END

which would be valid if CASE was a statement.