Hey, folks! Hope you all are doing well. In this article, we will be understanding the working of SQL CASE Statement.
Table of Contents
What is SQL CASE Statement?
Every programming language, such as Java, Python, C, C++, etc, contains various forms of conditional-statements to validate and choose the actions depending upon the conditions mentioned.
Let us understand this through an example.
In a retail store, various customers visit to buy different necessary food items and accessories. The retailer decides that he wants a software that would work on the below condition in an automated manner–
“Every customer will be granted a discount of 5% on the bought items if and only if the total cost of the items they are about to buy, exceeds 20$.”
So, the programmer or the developer would use the Case statement according to the preferred language and set the condition that if the total price exceeds 20$, then apply 5% discount on the bought items.
So, from the above example, you all must have got an idea about Case statement.
Yes, you have guessed it right!
SQL CASE statement helps us apply and set some conditions on the data and then provide the results/actions based upon the fulfillment of the condition. The CASE statement returns the value or performs the action that is mentioned against the fulfillment of the particular condition/conditions.
Now, let us understand the working of SQL CASE statement in the below section.
Syntax of CASE statement
SQL CASE statement returns a particular value if the first condition is met, else it checks for the next condition and the process continues.
CASE WHEN condition-1 THEN result-1 WHEN condition-2 THEN result-2 . . . WHEN condition-N THEN result-N ELSE default-result END;
- If no condition is true, the CASE statement returns the value specified in the ELSE portion.
- Moreover, if we do not provide any ELSE condition and if none of the conditions is TRUE, then the CASE statement returns a NULL value.
Implementing SQL Case Statement through examples
Here, we will implement the SQL Case statement with various SQL queries and clauses.
create table Info(id integer, Cost integer, city varchar(200)); insert into Info(id, Cost,city) values(1, 100,"Pune"); insert into Info(id, Cost,city) values(2, 50, "Satara"); insert into Info(id, Cost,city) values(3, 65,"Pune"); insert into Info(id, Cost,city) values(4, 97,"Mumbai"); insert into Info(id, Cost,city) values(5, 12,"USA");
Thus, we have created a table — ‘Info’ and added values to the columns created as above.
SQL SELECT query with CASE statement
We execute the SQL SELECT query to display the data values depending upon certain conditions stated in the CASE statement as mentioned in the below piece of code.
SELECT city, Cost, CASE WHEN Cost < 20 THEN 'Displaying price less than 20.' ELSE 'Price rates above 20' END AS Price_Rate FROM Info;
As seen above, when we execute the query, it will display the column values of ‘city’ and ‘Cost’. It creates a new column ‘Price_Rate’ to display the results of the conditions met.
SQL Update query with CASE statement
Now, let us try executing SQL Update query with CASE statement.
In the below example, we have passed an UPDATE query to alter the names of the cities passed in the CASE condition to a specified value.
UPDATE Info SET city = CASE city WHEN 'Pune' THEN 'POONA' WHEN 'USA' THEN 'UNITED NAIONS' ELSE 'INDIA' END;
After having clubbed CASE statement with the SQL queries, now let us try to execute it with some SQL Clauses.
SQL ORDER BY clause with CASE statement
Here, we have executed ORDER BY clause with CASE statement.
We select all the data values from the table – ‘Info’ using the SELECT query. Further, we add an ORDER BY clause with a CASE statement that states the following conditions.
- Display the data values in an ascending order of ‘Cost’ only if the city value is ‘Pune’.
Select * from Info ORDER BY CASE city WHEN 'Pune' THEN Cost End;
Feel free to comment below, in case you come across any doubt.
For more such posts related to SQL, please do visit SQL JournalDev.