SQL CASE Expression
In SQL, the CASE statement is used to perform conditional logic within a SQL query. It allows you to perform different actions based on specified conditions. The basic syntax for the CASE statement is as follows:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
You can have multiple WHEN clauses
followed by a single ELSE clause. The conditions are evaluated sequentially,
and the first condition that evaluates to true will result in the corresponding
result expression being returned. If none of the conditions are met, the result
of the ELSE clause will be returned.
Here's an example of a CASE statement
in a SQL query:
SELECT ProductName,
UnitPrice,
CASE
WHEN UnitPrice <= 10 THEN 'Low'
WHEN UnitPrice > 10 AND UnitPrice <= 50 THEN 'Medium'
WHEN UnitPrice > 50 THEN 'High'
ELSE 'Unknown'
END AS PriceCategory
FROM Products
In this example, a CASE statement is
used to categorize products based on their unit price. If the unit price is
less than or equal to 10, it's categorized as 'Low', if it's between 10 and 50
(inclusive), it's categorized as 'Medium', if it's greater than 50, it's
categorized as 'High', and if it doesn't meet any of these conditions, it's
categorized as 'Unknown'. The result of the CASE statement is given an alias
"PriceCategory" using the AS keyword to specify the column name in
the result set.
SQL CASE Examples
The following SQL goes through conditions and returns a value when the first condition is met:
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS
QuantityText
FROM OrderDetails;
The following SQL will order the customers by City. However, if City is NULL, then order by Country:
Example
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL
THEN Country
ELSE City
END);
0 Comments