SQL CASE Clause in SQL Queries
CASE clause can be used in
UPDATE and even in the
INSERT statement. In SQL, use columns in many different clauses like the
SELECT list, the
ORDER BY, or the
GROUP BY. What about the cases where the data is not coming from the same column?
Here is an example table:
|Name||height (meters)||weight (kilos)||age||smoke ?|
You want to create a report with the name of the person and their BMI value (body mass index). Here are the BMI Categories:
|Underweight||BMI ＜ 18.5|
|Normal Weight||18.5 ≦ BMI ＜ 25|
|Overweight||25 ≦ BMI ＜ 30|
|Obese||30 ≦ BMI|
This SQL query uses a
CASE clause to calculate the BMI index and, based on the BMI index value, shows the BMI Category.
Note the arithmetic expression weight / height ^ 2 calculates the body mass index. In SQL the operator ^ is used to calculate the power of a number.
SELECT name, CASE WHEN weight / height ^ 2 < 18.5 THEN ‘Underweight’ WHEN weight / height ^ 2 BETWEEN 18.5 AND 25 THEN ‘Normal Weight’ WHEN weight / height ^ 2 BETWEEN 25 AND 30 THEN ‘Overweight’ WHEN weight / height ^ 2 > 30 THEN ‘Obese’ END FROM person
We can also use
CASE in the
ORDER BY clause.Suppose you want to distribute a Covid vaccine to people ordered by a health risk index. You want people listed in this order:
- BMI greater than 30 (Obese)
- BMI between 25 and 30 (Overweight)
- Everyone else
You can execute this SQL query to obtain the ordered report:
SELECT name FROM person ORDER BY CASE WHEN weight / height ^ 2 > 30 THEN 1 WHEN smoker = TRUE THEN 2 WHEN weight / height ^ 2 BETWEN 25 AND 30 THEN 3 ELSE 4 END
Note. The query is ordered by an integer generated by a CASE statement: assigning 1 to the highest risk group, 2 and 3 to the next groups, and 4 for everyone else.
Here is the result:
CASE clause is very flexible and can be used in many different places of the SQL SELECT. In this Lesson you learned how to use the
CASE clause in the
SELECT list of columns and in the
ORDER BY. It can also be used in
WHERE, and even subqueries.
With this lesson, you are finished with the Basic tutorials. We covered many different topics, like logical and comparison operators, the
BETWEEN operators among other topics. Keep going, learn SQL and increase your skills!
IN THIS PAGE