How to Use a CASE Statement in the WHERE Clause? – Interview Question of the Week #239

Question: How to Use a CASE statement in the WHERE Clause?

Answer: First of all – CASE is not a statement but rather an expression so the right question should be How to Use a CASE expression in the WHERE Clause? In any case, let us see the answer to this question.

Lots of people think that case can be used in SELECT and ORDER BY clause but not in the WHERE clause. The truth is that it can be used anywhere in the entire SELECT statement. Let us see a very simple script which I have built based on the sample database.

SELECT InvoiceID, CustomerID,
AccountsPersonID, ContactPersonID
FROM [WideWorldImporters].[Sales].[Invoices]
WHERE 1 = CASE WHEN ContactPersonID = AccountsPersonID

You can see in the example above how we have used the CASE expression in the WHERE condition. Remember, the example above is just for the illustration purpose of the CASE in the WHERE clause. I know that I honestly do not need the CASE there and I could simplify the entire statement as WHERE ContactPersonID = AccountsPersonID.

I hope that the above example is very clear.

I request not to leave comments with the following two observations.
#1 CASE is not a statement but it is an expression – we know it.
#2 WHERE can be just simplified with ContactPersonID = AccountsPersonID – we know it.

Here are a few additional interview questions and answers which you may find interesting.

Reference: Pinal Dave (

SQL Sample Database, SQL Scripts, SQL Server
Previous Post
How to Create a Table Automatically in All Newly Created Databases? – Interview Question of the Week #238
Next Post
Do Stream Aggregate Operator Always Need Sort Operator? – Interview Question of the Week #240

Related Posts

3 Comments. Leave new

  • Thanks Pinal !for such a useful observation!

  • With the CASE expression, can a parameter / filter be optional in the WHERE clause?


    DECLARE @PersonId INT;
    SET @PersonId = 3;

    SELECT PersonID
    FROM dbo.Person
    WHERE Active = 1
    AND CASE ISNULL(@PersonId, 0) 0 THEN PersonId = @PersonId ELSE 0 END;

    The previous SELECT brings errors, but I’m wondering if such changes can be done.

  • BUT
    when i have more than one condition, i.e. i have @parametr and i try something like this:
    select … from table where
    when @parametr=1 then select data from table1
    when @parametr=2 then select data from table2

    real example: (with @parametr)
    select something from table where SomethingIsActive=1 and
    when @parametr=1 then somethingNr in (1,2,3)
    when @parametr=2 then somethingNr in (1,2,3,4,5,6)


Leave a Reply