Table of Contents
MySQL can utilize a Case Statement with Multiple Conditions by allowing users to define specific conditions and corresponding actions to be taken in a query. The CASE statement evaluates each condition, and if it is true, it executes the corresponding action. This allows for more efficient and organized data retrieval and manipulation, as multiple conditions can be checked and different actions can be taken based on the results. This feature also allows for more complex logic to be implemented in a single query, reducing the need for multiple separate queries.
You can use the following syntax in MySQL to use a CASE statement with multiple conditions:
SELECT id, team, position,
(CASE WHEN (team = 'Mavs' AND position = 'Guard') THEN 101
WHEN (team = 'Mavs' AND position = 'Forward') THEN 102
WHEN (team = 'Spurs' AND position = 'Guard') THEN 103
WHEN (team = 'Spurs' AND position = 'Forward') THEN 104
END) AS team_pos_ID
FROM athletes;
This particular example uses a CASE statement to create a new column named team_pos_ID that contains the following values:
- 101 if the team column is ‘Mavs’ and the position column is ‘Guard’
- 102 if the team column is ‘Mavs’ and the position column is ‘Forward’
- 103 if the team column is ‘Spurs’ and the position column is ‘Guard’
- 104 if the team column is ‘Spurs’ and the position column is ‘Forward’
The following example shows how to use this syntax in practice.
Example: How to Use Case Statement with Multiple Conditions in MySQL
Suppose we have the following table named athletes that contains information about various basketball players:
-- create table
CREATE TABLE athletes (
id INT PRIMARY KEY,
team TEXT NOT NULL,
position TEXT NOT NULL,
points INT NOT NULL
);
-- insert rows into table
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 22);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 36);
INSERT INTO athletes VALUES (0004, 'Spurs', 'Guard', 18);
INSERT INTO athletes VALUES (0005, 'Spurs', 'Forward', 40);
INSERT INTO athletes VALUES (0006, 'Spurs', 'Forward', 25);
-- view all rows in table
SELECT * FROM athletes;
Output:
+----+-------+----------+--------+
| id | team | position | points |
+----+-------+----------+--------+
| 1 | Mavs | Guard | 15 |
| 2 | Mavs | Guard | 22 |
| 3 | Mavs | Forward | 36 |
| 4 | Spurs | Guard | 18 |
| 5 | Spurs | Forward | 40 |
| 6 | Spurs | Forward | 25 |
+----+-------+----------+--------+ Suppose that we would like to create a new column named team_pos_ID that contains a specific value based on the corresponding values in both the team and position columns.
We can use a CASE statement with multiple conditions to do so:
SELECT id, team, position,
(CASE WHEN (team = 'Mavs' AND position = 'Guard') THEN 101
WHEN (team = 'Mavs' AND position = 'Forward') THEN 102
WHEN (team = 'Spurs' AND position = 'Guard') THEN 103
WHEN (team = 'Spurs' AND position = 'Forward') THEN 104
END) AS team_pos_ID
FROM athletes; Output:
+----+-------+----------+-------------+
| id | team | position | team_pos_ID |
+----+-------+----------+-------------+
| 1 | Mavs | Guard | 101 |
| 2 | Mavs | Guard | 101 |
| 3 | Mavs | Forward | 102 |
| 4 | Spurs | Guard | 103 |
| 5 | Spurs | Forward | 104 |
| 6 | Spurs | Forward | 104 |
+----+-------+----------+-------------+ Notice that the new team_pos_ID column contains the following values:
- 101 if the team column is ‘Mavs’ and the position column is ‘Guard’
- 102 if the team column is ‘Mavs’ and the position column is ‘Forward’
- 103 if the team column is ‘Spurs’ and the position column is ‘Guard’
- 104 if the team column is ‘Spurs’ and the position column is ‘Forward’
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL:
MySQL: How to Use DELETE with INNER JOIN
MySQL: How to Delete Rows from Table Based on id
MySQL: How to Delete Duplicate Rows But Keep Latest