Yogesh Chauhan's Blog

Can we execute conditions in SQL?

in SQL/MySQL on October 11, 2019

SQL CASE Statement

Well to answer the question I can say yes and no because yes, we can apply some basic conditions in SQL but it's not advanced as any other programming language because it's a query language.

The CASE statement works similar to CASE statement in any programming language. It's going to check each condition one by one and it's going to return a value when the first condition is met. We can compare it to IF-ELSE or for loop statement as far we think about comparison to the condition. So, the way for loop or IF-ELSE loop ends after the condition is TRUE, similarly this CASE statement is going to stop executing once the condition is TRUE. If the condition is FALSE, it's going to execute till the last condition. And if none of the CASE returns TRUE then it will go to ELSE and execute that line of code.

What if we forget to add ELSE part and all of the conditions are FALSE?

??

It will return NULL. 

Let's take a look at the syntax:


CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ....
    ELSE result
END;

Let's take look at the example.

Let's assume you have a online shopping website in a customer needs to order more than 11 quantity in order to qualify for frees shipping. So, let's see how we can execute that using CASE.

NOTE: I advise you to open the DEMO from the link given at the end of this article and check the results of the query as we go through. 


SELECT OrderID, Quantity,
CASE 
    WHEN Quantity > 11 THEN 'Order Qualified'
    WHEN Quantity = 11 THEN 'Need One More Quantity To Qualify'
    ELSE 'Not Qualified as the quantity is less than 11'
END AS QuantityText
FROM orderDetails

As you can see we are selecting 2 columns and 3rd column as the result of the CASE for each item. So, the CASE 1 checks if the quantity is greater than 11. If so, it will add Order Qualified in the row for that specific item. If that CASE is FALSE then it checks for 2nd CASE and so on. If none of the cases matches, it will display "Not Qualified as the quantity is less than 11" in QuantityText column for that specific row.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to create a new HTML element programmatically using JavaScript?HTMLHow to compress images with gulp in WordPress?WordPressHow to create Flickering Texts using CSS?CSSWhat are Identifiers in JavaScript?JavaScriptHow to create a placeholder loader (throbber) using CSS?CSSWhat is the difference between let and var in Swift?Swift