Can you use DELETE with INNER JOIN in MySQL?

---

You can use the following syntax in MySQL to delete rows from a specific table after performing an inner join:

DELETE athletes1
FROM athletes1
INNER JOIN athletes2 ON athletes1.id = athletes2.id
WHERE athletes2.conference = 'East';

This particular example performs an inner join between the tables named athletes1 and athletes2 based on matching values in the id columns.

It then deletes all rows from athletes1 where the value in the conference column of athletes2 has a value of East.

The following example shows how to use this syntax in practice.

Example: How to Use DELETE with INNER JOIN in MySQL

Suppose we create the following table named athletes1 that contains information about various basketball players:

-- create table 
CREATE TABLE athletes1 (
  athleteID INT PRIMARY KEY,
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes1 VALUES (0001, 'Mavs', 22);
INSERT INTO athletes1 VALUES (0002, 'Celtics', 14);
INSERT INTO athletes1 VALUES (0003, 'Nuggets', 37);
INSERT INTO athletes1 VALUES (0004, 'Knicks', 19);
INSERT INTO athletes1 VALUES (0005, 'Warriors', 26);
INSERT INTO athletes1 VALUES (0006, 'Thunder', 40);

-- view all rows in table
SELECT * FROM athletes1;

Output:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  2 | Celtics  |     14 |
|  3 | Nuggets  |     37 |
|  4 | Knicks   |     19 |
|  5 | Warriors |     26 |
|  6 | Thunder  |     40 |
+----+----------+--------+

Then suppose we create another table named athletes2 that contains information about more basketball players:

-- create table 
CREATE TABLE athletes2 (
  athleteID INT PRIMARY KEY,
  team TEXT NOT NULL,
  conference TEXT NOT NULL
);

-- insert rows into table
INSERT INTO athletes2 VALUES (0001, 'Mavs', 'West');
INSERT INTO athletes2 VALUES (0002, 'Celtics', 'East');
INSERT INTO athletes2 VALUES (0003, 'Nuggets', 'West');
INSERT INTO athletes2 VALUES (0004, 'Knicks', 'East');
INSERT INTO athletes2 VALUES (0005, 'Warriors', 'West');
INSERT INTO athletes2 VALUES (0006, 'Thunder', 'West');

-- view all rows in table
SELECT * FROM athletes2;

Output:

+----+----------+------------+
| id | team     | conference |
+----+----------+------------+
|  1 | Mavs     | West       |
|  2 | Celtics  | East       |
|  3 | Nuggets  | West       |
|  4 | Knicks   | East       |
|  5 | Warriors | West       |
|  6 | Thunder  | West       |
+----+----------+------------+

Suppose that we would like to use INNER JOIN to join the rows from each table based on the id column, then use DELETE to delete all rows from athletes1 where the value in the conference column of athletes2 is East.

We can use the following syntax to do so:

-- join tables then delete rows from athletes1 who are in East conference
DELETE athletes1
FROM athletes1
INNER JOIN athletes2 ON athletes1.id = athletes2.id
WHERE athletes2.conference = 'East';

-- view all rows in updated athletes1 table
SELECT * FROM athletes1;

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  3 | Nuggets  |     37 |
|  5 | Warriors |     26 |
|  6 | Thunder  |     40 |
+----+----------+--------+