AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (12 Blogs)
  • MySQL (24 Blogs)
  • SQL Essentials Training and Certification (3 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

Learn How To Use CASE Statement In SQL

Published on Aug 29,2019 47 Views
8 / 16 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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. 

SQL-CASE In SQL-EdurekaThe following topics will be covered in this article:

    1. What is CASE in SQL?
    2. CASE Syntax
    3. Simple CASE Expression Example
    4. Search CASE Expression Example

What is CASE in SQL?

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 Syntax

CASE
	    WHEN Condition1 THEN Result1
	    WHEN Condition2 THEN Result2
		WHEN Condition3 THEN Result3
	    WHEN ConditionN THEN ResultN
	    ELSE Result;

Now, since I have told you, what is the syntax of the CASE statement in SQL. Let us see how to use the CASE statement, with values or with a search condition.

Consider the following table for the example:

StudentIDFirstNameAgeCity
1Rohan14Hyderabad
2Sonali21Bengaluru
3Ajay13Lucknow
4Geeta25Lucknow
5Shubham20Delhi

Simple CASE Expression 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;

On executing the above query, you will see the following output:

StudentIDCityAgeValue
1HyderabadAge is below than 20
2BengaluruAge is greater than 20
3LucknowAge is below than 20
4LucknowAge is greater than 20
5DelhiAge is equal to 20

Search CASE Expression Example

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:

FirstNameAgeCity
Ajay13Lucknow
Rohan14Hyderabad
Shubham20Delhi
Sonali21Bengaluru
Geeta25Lucknow

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.

Comments
0 Comments

Browse Categories

Subscribe to our Newsletter, and get personalized recommendations.