Yes, it kind of is same as IF/ELSE statement but instead of writing IF/ELSE, we use WHEN, THEN and ELSE. I know it sounds different but trust me it pretty much has the same function and it all comes under the SQL CASE Statement.
CASE returns records with your conditions by allowing you to include if/then statements in your query. In simple words what this statement does is, it goes through conditions i.e. cases from top to bottom and which ever condition first met, it'll return the value. It is just like the if-then-else statement. Now important thing to keep in mind is that, if there is no ELSE block and all the conditions are false, it will return a NULL value.
This expression can be used in statements like
UPDATE or in clauses like
ORDER BY, and
HAVING. This gives a lot power in your hand because now you can control the execution / deletion of different sets of statements which meets the requirement.
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
Now let's take a closer look at this with the help of an example.
Note: I'm using Google BigQuery.
Here I've a sample Inventory data and the schema is as follows:
|Field name||Type||Mode||Collation||Policy Tags||Description|
Here is the preview of the data which is inside our table :
We are going to write a small query which will return the
QuantityAvailable from our Inventory table.
- To keep it simple, I'm using Case statement with
SELECTand to remove any further confusion I'm writing first a Syntax structure of my query so people who are less familiar with SQL can understand :
SELECT columnName1, columnName2, columnName3, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result END FROM `tableName`;
Now begins the real Query :
SELECT StoreName, neighborhood, QuantityAvailable, CASE WHEN QuantityAvailable >= 10 THEN "The Quantity is above 10" WHEN QuantityAvailable < 10 THEN "The Quantity is less 10" ELSE "Out of Stock" END FROM `forward-glass-347505.Sales.inventory`;
Here are the result of our query:
This query shows Store names and the neighborhood in which they are in and how much Stock they have of some imaginary stuff. In the first condition: if the quantity is above or equal to 10, it will print out "The Quantity is above 10" and in the second case, if the quantity is less than 10 it will print out "The quantity is less than 10". If the above condition do not match i.e. if the quantity is zero or there is no data available, the else part of the query will execute and the result would be "Out of Stock".
Now what if we change the conditions a little, like :
SELECT StoreName, neighborhood, QuantityAvailable, CASE WHEN QuantityAvailable < 0 THEN "The Quantity is above 10" WHEN QuantityAvailable >20 THEN "The Quantity is less 10" ELSE "Out of Stock" END FROM `forward-glass-347505.Sales.inventory`;
The output would be "Out of Stock" because all the conditions are false thus Query printed the ELSE part like this :
Let's remove the ELSE part and see what would happen:
SELECT StoreName, neighborhood, QuantityAvailable, CASE WHEN QuantityAvailable < 0 THEN "The Quantity is above 10" WHEN QuantityAvailable >20 THEN "The Quantity is less 10" END FROM `forward-glass-347505.Sales.inventory`;
Here's the output:
As I mentioned above: if there is no
ELSE part and the above conditions are false, the query will return
That's pretty much it about the CASE statement. If you're more curious, you can also check out SQL IF statement or wait for my next article. Have any query? Comment it down.
Did you find this article valuable?
Support Abhishek Kumar by becoming a sponsor. Any amount is appreciated!