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.

Solarwinds
SELECT InvoiceID, CustomerID,
AccountsPersonID, ContactPersonID
FROM [WideWorldImporters].[Sales].[Invoices]
WHERE 1 = CASE WHEN ContactPersonID = AccountsPersonID
THEN 1
ELSE 0 END
GO

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 (https://blog.sqlauthority.com)

Solarwinds
, ,
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

2 Comments. Leave new

  • Thanks Pinal !for such a useful observation!

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

    Example:

    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.

    Reply

Leave a Reply

Menu