We have the answers to your questions! - Don't miss our next open house about the data universe!

SQL CASE: What is this query and how do I use it?

- Reading Time: 3 minutes
sql case

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.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox