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 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #4 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
How to enable and disable button using JavaScript?JavaScriptHow to change your WordPress database prefix?WordPressHow to use @supports rule in CSS?CSSHow to solve “framework not found” error in Swift?SwiftHow to Make CSS Lists Bullets Smaller?CSSAmpersand (Parent Selector) in SCSS (Sass)SCSS