sql

SQL CASE Statement Example

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.

SQL CASE - data inserted
Table student-created & data inserted

Now, using the select statement, you can see the data that has been successfully inserted into the table.

SQL CASE - Data inserted into the student table
Data inserted into the student 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’.

SQL Case Statement is used.

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.

SQL CASE and Update Statement

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‘.

SQL CASE - GROUP BY Clause
SQL CASE and GROUP BY Clause

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.

SQL CASE - ORDER BY Clause
SQL CASE and ORDER BY Clause

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>
SQL Case and Having Clause

4. Summary

In this example, we learned the following things:

  1. The functionality of SQL CASE Statement.
  2. 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.

Download
You can download the full source code of this example here: SQL CASE Statement Example

Last updated on Feb. 24th, 2022

Simran Koul

Simran has graduated as a Bachelor of Engineering in Computer Science from Chitkara University. She has undergone a 6-months long comprehensive industrial training at the reputed Centre for Development of Advanced Computing (C-DAC), where she worked on a project including the likes of Java, JSP, Servlets while the UI-UX through the pioneering HTML, CSS and JS. Her inquisitive nature and the seed of curiosity keeps her on the toes to find material to write about. Along with her interests in Software Development, she is an ardent reader and always ready-to-write writer.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button