This example will focus on the SQL Case Statement.
In any programming language, you would know that the control statements are the most essential part of the programming language. Just like if-else in any of the programming languages, SQL also has the control statements.
1. About SQL CASE Statement
There will be a list of conditions, which can either be true or false. What the CASE statement does is that it goes through every condition one by one, and returns only the value for the first true statement.
If there is no statement true, then it moves onto the else part. And, if even the else part is false, then it returns NULL.
2. SQL CASE Syntax
The SQL CASE Syntax is given as follows:
SELECT CASE expr
WHEN expr1 THEN X
When expr2 THEN Y
...
ELSE Z
END AS column_name
Here, expr, expr1, and expr2 represent expressions (conditions), while X, Y, and Z represent the statements that need to be executed when their respective conditions are true.
For example: if expr1 is true, then X will be returned. If expr2 is true, then Y will be returned. And, if-else is true, then Z will be returned. If none of the expressions/conditions match to true, then NULL will be returned.
Note:
- You can have many expressions, i.e. conditions to check.
- CASE Statement can be used with various other clauses like the Order By Clause, or the Group By Clause.
Syntax to select particular columns, and using SQL CASE statement is as follows:
SELECT column1, column2 CASE expr
WHEN expr1 THEN X
When expr2 THEN Y
...
ELSE Z
END AS column_name
3. SQL CASE Example
Let us now see all the different use-cases of the SQL CASE statement.
3.1 Creating the table & Insert sample data into the table
Let us create a table – student – with the columns as follows: roll number, name, and gender.
Now, using the select statement, you can see the data that has been successfully inserted into the table.
3.2 SQL CASE Example
Let us segregate the rows with gender value ‘m‘ should correspond to ‘Male‘ while segregating the rows with gender value ‘f‘ should correspond to ‘Female’.
You can see the genderDetail column comprising of the values ‘MALE‘ and ‘FEMALE‘, and compare it with the table below that to see the values of the gender column.
3.3 SQL CASE with UPDATE statement
Let us now use the update statement as well as the SQL CASE statement together. We are going to change the values of gender from lowercase to uppercase.
You can clearly see that the gender column now has uppercase characters. The changes are made to the gender column since we did not create an alias.
3.4 SQL CASE with GROUP BY Clause
We can also use the SQL CASE statement with the GROUP BY Clause. In this case, we are going to group the students by their roll number. For roll numbers less than 10, they will be allocated ‘FRONT SEAT‘. While for the roll numbers greater than or equal to 10, they will be allocated ‘BACK SEAT‘.
As a result of the query, you can see that the seats have been allocated to the students on the basis of the roll number.
3.5 SQL CASE with ORDER BY Clause
Now, we will use the SQL CASE statement with the ORDER BY clause. In this case, we have sorted the females with descending order of their roll numbers, while the males with the ascending order of their names.
Here, you see that the female is ordered first since there is only 1 female in the table data. Then subsequently, both the males are ordered in the ascending order of their names.
3.6 SQL CASE in a HAVING Clause
Now, we will use the SQL CASE statement in a HAVING clause. In this case, we inserted three more records. We use group by clause at the gender column and restrict the record count for female set it as 1, while for the male set as 4.
case in having
insert into student values(100,'Allen','m');
insert into student values(122,'Amy','f');
insert into student values(221,'Arun','m');
mysql> select * from student;
+--------+-------+--------+
| rollno | name | gender |
+--------+-------+--------+
| 1 | alex | M |
| 12 | tina | F |
| 22 | amex | M |
| 100 | Allen | m |
| 122 | Amy | f |
| 221 | Arun | m |
+--------+-------+--------+
6 rows in set (0.00 sec)
mysql> select gender, count(*) from student group by gender ;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M | 4 |
| F | 2 |
+--------+----------+
2 rows in set (0.00 sec)
mysql> SELECT Gender, COUNT(*)
-> FROM student
-> GROUP BY Gender
-> HAVING COUNT(*) >
-> CASE
-> WHEN Gender = 'M'
-> THEN 4
-> ELSE 1
-> END;
+--------+----------+
| Gender | COUNT(*) |
+--------+----------+
| F | 2 |
+--------+----------+
1 row in set (0.00 sec)
mysql>
4. Summary
In this example, we learned the following things:
- The functionality of SQL CASE Statement.
- Usage of SQL CASE Statement in different ways.
5. Download the source code
You can download the scripts from here, and replicate them on your SQL command-line client or workbench.
You can download the full source code of this example here: SQL CASE Statement Example
Last updated on Feb. 24th, 2022
