How MySQL Deletes With the Delete Join Statements

This tutorial will discuss how to use the MySQL DELETE clause in a JOIN statement to delete data from multiple tables that meet the specified condition.

The purpose of this tutorial is to help you understand how to use the DELETE clause along with the JOIN clause to remove data at once. If you are new to MySQL, consider our other tutorials, such as JOINS and DROP tables tutorials.

Let us get started.

Basic Usage: Delete With Inner Join

The first Delete method we will discuss is how to use the MySQL DELETE clause inside an INNER JOIN statement to remove data from rows matching another table.

The general syntax to implement such a query is shown below:

DELETE tbl1, tbl2 FROM tbl1 INNER JOIN tbl2 ON tbl1.col = tbl2.col WHERE [condition];

Let me assure you that it is easier than it looks. Let me explain:

We start by specifying the tables from which we want to remove the data. Tables are specified between the DELETE and FROM clause.

In the second part, we specify the condition for the matching rows in the set tables. For example:

tbl1.col = tbl2.col

Finally, we set the WHERE condition that determines the rows in the specified tables to be deleted.

Example Use Case

Let me use an example to illustrate how we can use the DELETE clause and INNER JOIN to remove rows from multiple tables. Consider the queries below:

CREATE SCHEMA society;
USE society;
DROP TABLE IF EXISTS users, contacts;
CREATE TABLE users(
user_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100),
last_name VARCHAR(100),
state VARCHAR(50)
);
CREATE TABLE contacts(
home_id INT PRIMARY KEY AUTO_INCREMENT,
tel VARCHAR(50),
address VARCHAR(255)
);
INSERT INTO users(first_name, last_name, state) VALUES ("John", "Muller", "Colorado"), ("Mary", "Jane", "California"), ("Peter", "Quill", "New York");
INSERT INTO contacts(tel, address) VALUES ("303-555-0156", "281 Denver, Colorado"), ("661-555-0134", "302 Drive, Bakersfield"), ("516-555-0148", "626 Est Meadow, NYC");

Once we have such data, we can illustrate how to use DELETE with INNER JOIN as shown in the query below:

DELETE society.users, society.contacts FROM society.users INNER JOIN contacts ON user_id=home_id WHERE user_id=3;

The query above will display the result as shown below:

two rows affected in 7 ms, which indicates that two rows have been removed.

Delete with LEFT JOIN

The second Delete method we will discuss is to use the LEFT JOIN. The general syntax for this delete type is as shown below:

DELETE tbl1 FROM tbl1 LEFT JOIN tbl2 ON tbl1.col = tbl2.col WHERE tble.col IS NULL;

FOR DELETE with LEFT JOIN, we specify only one table—unlike the INNER JOIN where we specified two tables.

Consider the entire query below:

USE society;
DROP TABLE IF EXISTS users, contacts;
CREATE TABLE users(
user_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100),
last_name VARCHAR(100),
state VARCHAR(50)
);
CREATE TABLE contacts(
home_id INT PRIMARY KEY AUTO_INCREMENT,
tel VARCHAR(50),
address VARCHAR(255)
);
INSERT INTO users(first_name, last_name, state) VALUES ("John", "Muller", "Colorado"), ("Mary", "Jane", "California"), ("Peter", "Quill", "New York"), ("Mystic", "Arts", "South Carolina");
INSERT INTO contacts(tel, address) VALUES ("303-555-0156", "281 Denver, Colorado"), ("661-555-0134", "302 Drive, Bakersfield"), ("516-555-0148", "626 Est Meadow NYC"), ("843-555-0105", null);

DELETE users FROM users LEFT JOIN contacts ON user_id = home_id WHERE address IS NULL;
SELECT * FROM users;

Once we execute the above query, the user whose address is null after the JOIN is deleted and the output result is as shown below:

Conclusion

In this tutorial, we discussed how to use MySQL DELETE with JOIN statements to delete data from multiple tables.

How MySQL Deletes With the Delete Join Statements

Basic Usage: Delete With Inner Join

Example Use Case

Delete with LEFT JOIN

Conclusion

About the author

John Otieno