SQL programming language is a valuable tool for developers and experts. Thanks to a multitude of queries, it is possible to manipulate data and relational databases. Among the essential commands, there is SQL CASE. What is this query used for? And how to use it? Discover the answers in this article.
Case SQL : What is this query and how do I use it?
In computer language, the CASE/WHEN command serves the same purpose as the IF/ELSE function. Namely, to return a result from among several possibilities.
Depending on the syntax used, CASE can be used to :
1º Compare data with a set of possible results. In this case, here’s the CASE SQL syntax:
CASE
WHEN expression THEN result
ELSE other result
END
2 º Determine a result by constructing several Boolean expressions (true or false). The syntax is as follows:
CASE
WHEN Boolean expression THEN result
ELSE other result
END
Good to know: in both cases, the ELSE clause is optional. This allows you to define a value in case the WHEN conditions are not met.
Beyond these simplified syntaxes, the SQL CASE query is often used with other commands.
What are the other commands to use with SQL CASE?
To enrich the database, the SQL CASE query can be used with other commands. Here are the main ones and their different functionalities:
SELECT: This is one of the most common SQL queries. It allows you to select columns within a table.
UPDATE: This command is used to update a column.
DELETE: It is used to delete one or more rows.
ORDER BY: This query is used to sort the data in the table (ascending or descending order).
GROUP BY: This allows you to group data based on a defined condition.
INSERT INTO: If you want to add more information to your data table.
SUM: It is used to calculate sums (of a product, a price, etc.).
HAVING: To restrict rows.
That being said, we will now see some concrete usage examples.
3 examples of using CASE SQL
To help you better understand the use of SQL CASE, here are several concrete examples.
For each of them, we will use this “Purchase” table. The company’s goal is to determine the right price. To do this, it relies on the achieved profit margin.
| ID | Product Name | Margin | Quantity | Unit Price |
|---|---|---|---|---|
| 1 | Product 1 | 1 | 1 | 50 |
| 2 | Product 2 | 1.25 | 3 | 70 |
| 3 | Product 3 | 0.75 | 2 | 60 |
| 4 | Product 4 | 1.5 | 2 | 80 |
Personalized message
The goal is to inform decision-makers about the pricing positioning of each product. To achieve this, you can use the SQL CASE query.
Here is the syntax:
SELECT id, product name, margin, quantity, unit price,
CASE
WHEN margin > 1 THEN ‘High Price’
WHEN margin < 1 THEN ‘Low Price’
ELSE ‘Normal Price’
END
FROM `Purchase`
And the result:
| ID | Product Name | Margin | Quantity | Unit Price | CASE |
|---|---|---|---|---|---|
| 1 | Product 1 | 1 | 1 | 50 | Normal Price |
| 2 | Product 2 | 1.25 | 3 | 70 | High Price |
| 3 | Product 3 | 0.75 | 2 | 60 | Low Price |
| 4 | Product 4 | 1.5 | 2 | 80 | High Price |
Unit price change
To adjust its pricing policy, the company wishes to modify its prices based on its margin: reduce by 10% when the margin is positive and increase by 10% when it is negative.
Once again, the use of conditions and the SQL CASE command allows for quick modification of the database.
Here is the syntax:
SELECT id, product name, margin, quantity, unit price,
CASE
WHEN margin > 1 THEN prix unitaire * 0.9
WHEN margin < 1 THEN prix unitaire * 1.1
ELSE unit price
END
FROM `Purchase`
| ID | Product Name | Margin | Quantity | Unit Price |
|---|---|---|---|---|
| 1 | Product 1 | 1 | 1 | 50 |
| 2 | Product 2 | 1.25 | 3 | 63 |
| 3 | Product 3 | 0.75 | 2 | 66 |
| 4 | Product 4 | 1.5 | 2 | 72 |
Quantity update
In addition to SELECT, the CASE SQL query can also be used with other commands, such as UPDATE.
To help you visualize this query, here’s a hypothetical scenario.
For every product with a positive margin, the company offers an additional product. In this case, you’ll need to update the table.
Here’s how to do it:
UPDATE `purchase
SET `quantity` = (
CASE
WHEN margin > 1 THEN ‘Quantity + 1’.
ELSE ‘Quantity
END
)
And the result:
| ID | Product Name | Margin | Quantity | Unit Price |
|---|---|---|---|---|
| 1 | Product 1 | 1 | 1 | 50 |
| 2 | Product 2 | 1.25 | 4 | 70 |
| 3 | Product 3 | 0.75 | 2 | 60 |
| 4 | Product 4 | 1.5 | 3 | 80 |
These are just a few simplified examples. If you’d like to find out more about the various use cases for CASE SQL queries, take our training course at DataScientest.
Develop your computer language skills with DataScientest
Beyond the CASE SQL query, the structured query language is packed with a multitude of commands for manipulating databases. To master them all, training is essential. That’s why we offer a range of training courses at Datascientest.
