Learn How To Use CASE Statement In SQL

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 following topics will be covered in this article:

1. 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:

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:

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:

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.