Understanding SQL CASE Statement

Filed Under: SQL
SQL CASE STATEMENT

Hey, folks! Hope you all are doing well. In this article, we will be understanding the working of SQL CASE Statement.


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.

Syntax:

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.

Let us first create a table using SQL Create query and insert values into the table through SQL Insert query.

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.

Example:

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.

Output:

Case Statement Example 1
Case Statement Example

SQL Update query with CASE statement

Now, let us try executing SQL Update query with CASE statement.

Example:

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;

Output:

Case Statement Example 2
Case Statement Example 2

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’.

Example:

Select *
 from Info
 ORDER BY  CASE city
WHEN 'Pune' THEN Cost End;

Output:

Case Statement Example 3
Case Statement Example 3

Conclusion

That’s all for this topic. I recommend the readers to implement CASE Statement with GROUP BY clause, HAVING clause, DELETE query, etc.

Feel free to comment below, in case you come across any doubt.

For more such posts related to SQL, please do visit SQL JournalDev.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages