MySQL: How to Use Case Statement with Multiple Conditions


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’

Note: We used the AND operator within the WHEN statement to check if multiple conditions were met but you could instead use the OR operator if you’d like to check if at least one of multiple conditions were met.

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

Leave a Reply

Your email address will not be published. Required fields are marked *