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.
conditions sql query