CASE Statement in SQL
Can we use CASE Statement in Where clause?
It's also important to understand that in SQL, CASE is an expression and not an executable "control-of-flow" statement. Thus, the CASE expression, when evaluated, just returns a value which can then be tested or otherwise combined with other expressions (in statements). You cannot use the result of a CASE expression directly as the predicate of a WHERE clause. Thus:
is not legal, because the WHERE clause would be the equivalent of
which is not legal syntax. Instead, the code could read:
and that's legal.
Use the CASE Statement in a SQL SELECT Clause
SQL Server provides a mechanism for returning different values in a SELECT clause based on Boolean conditions: the CASE statement. This statement resembles Visual Basics Select Case statement.
The SQL CASE statement has WHEN, THEN, and ELSE clauses along with an END terminator. The syntax is:
WHEN [value | Boolean expression] THEN [return value]
[ELSE [return value]]
The [expression] is optional and contains a table column or a variable. When you specify [expression] directly after the CASE, you must populate the [value] parameter in the WHEN clause:
DECLARE @TestVal int
SET @TestVal = 3
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
SQL Server compares this value to the expression and when the values match, it returns the THEN clauses [return value]. If none of the WHEN clauses equates to true, SQL Server returns the [return value] in the optional ELSE clause. If the ELSE clause is omitted and no value is matched, NULL is returned.
If you dont specify [expression], you must include the [Boolean expression] in the WHEN clause. This can contain any valid Boolean expression SQL Server allows:
DECLARE @TestVal int
SET @TestVal = 5
WHEN @TestVal <=3 THEN 'Top 3'
Using CASE in an ORDER BY clauseThe following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value.
SELECT BusinessEntityID, SalariedFlag
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
ELSE CountryRegionName END;
Using CASE in a HAVING clauseThe following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement. The statement returns the the maximum hourly rate for each job title in the HumanResources.Employee table. The HAVING clause restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;