Question: Can CASE be used in a WHERE clause? Yes. CASE returns a value, and WHERE can compare that value as part of its predicate.
First, a small terminology correction: in T-SQL this is a CASE expression. It chooses a result value; it isn’t a separate control-of-flow statement. The original WideWorldImporters example makes its use in WHERE easy to see.
SELECT InvoiceID, CustomerID, AccountsPersonID, ContactPersonID
FROM WideWorldImporters.Sales.Invoices
WHERE 1 = CASE WHEN ContactPersonID = AccountsPersonID
THEN 1 ELSE 0 END;
-- Simpler expression of this same requirement.
SELECT InvoiceID, CustomerID, AccountsPersonID, ContactPersonID
FROM WideWorldImporters.Sales.Invoices
WHERE ContactPersonID = AccountsPersonID;The first query produces 1 when the two person IDs are equal and 0 otherwise. WHERE keeps rows for which that value equals 1. The second expresses this same requirement directly. If either ID is NULL, neither predicate is true, so the row is excluded.
Yes, I know the CASE is unnecessary in this example! Its purpose is to show the syntax, not to claim the longer predicate is a tuning improvement. CASE is useful when the value being compared really depends on a condition.
THEN and ELSE return values. They cannot return complete WHERE predicates or pieces of SQL syntax. When a requirement is naturally several Boolean conditions, AND and OR may be clearer. Applying a CASE around an indexed column can also make a useful seek harder, so check the actual plan rather than assuming extra syntax is free.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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?
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.
BUT
when i have more than one condition, i.e. i have @parametr and i try something like this:
select … from table where
case
when @parametr=1 then select data from table1
when @parametr=2 then select data from table2
end
?
real example: (with @parametr)
select something from table where SomethingIsActive=1 and
case
when @parametr=1 then somethingNr in (1,2,3)
when @parametr=2 then somethingNr in (1,2,3,4,5,6)
end