SQL Essentials Training & Certification
- 5k Enrolled Learners
- Self Paced
In today’s world, where a humongous amount of data gets generated every day, we have to make sure that we have the ability to retrieve data based on conditions. So, in this article on CASE in SQL, I will discuss the CASE statement which is used to retrieve data based on conditions.
The CASE statement is used to retrieve data based on a few conditions. So, once the condition is met, then it will stop reading the data and return the required results. In a scenario, where no conditions are met, then it returns the values from the ELSE clause. Apart from this, if there is no ELSE part, then no conditions are met and will return NULL.
CASE WHEN Condition1 THEN Result1 WHEN Condition2 THEN Result2 WHEN Condition3 THEN Result3 WHEN ConditionN THEN ResultN ELSE Result;
Consider the following table for the example:
Simple CASE is used in SQL, to return the data based on a few conditions and return a value when the first condition is met.
SELECT StudentID, City, CASE WHEN Age > 20 THEN "Age is greater than " WHEN Age = 20 THEN "Age is equal to 20" ELSE "Age is below 20" END AS AgeValue FROM Students;
|1||Hyderabad||Age is below than 20|
|2||Bengaluru||Age is greater than 20|
|3||Lucknow||Age is below than 20|
|4||Lucknow||Age is greater than 20|
|5||Delhi||Age is equal to 20|
Search CASE is used in SQL, to return the data based on a condition present in the CASE statement. Consider a scenario, where you have to order the students, by Age. However, if Age is between 15 and 18, then you have to order by City
SELECT FirstName, Age, City FROM Students ORDER BY ( CASE WHEN Age BETWEEN 15 AND 18 THEN City ELSE Age END );
Since our above table “Students” has no NULL value present, on executing the above query, you will see the following output:
With this, we come to an end to this article on CASE in SQL. I hope you understood how to use the CASE statement to retrieve data based on conditions.. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this article on “CASE in SQL”and I will get back to you.