Home » Core Java » sql » SQL CASE Statement Example

About Simran Koul

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.

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 OrderBy Clause, or the GroupBy 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, 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 into 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 no. 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 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.

4. Summary

In this example, we learnt 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 it on your SQL command-line client or workbench.

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

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

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