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
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.

• How to Create a Table Automatically in All Newly Created Databases? – Interview Question of the Week #238
• How to Skip Top N Rows in SQL Server? – Interview Question of the Week #237
• How to Write in Text File from T-SQL in SQL Server? – Interview Question of the Week #236
• How to Remove Quotes of QUOTENAME? – Interview Question of the Week #235
• Can an Index Reduce Performance of SELECT Query? – Interview Question of the Week #234
• How to Find Definition of Computed Column in SQL Server using T-SQL? – Interview Question of the Week #233
• What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232

Reference: Pinal Dave (https://blog.sqlauthority.com)