Delete Records from Multiple Tables in MySQL Using a Single Query

In MySQL, the DELETE JOIN statement lets you delete records from multiple tables in a query. This tutorial will guide you through the process of using the DELETE JOIN syntax to delete records from multiple tables at once. It can be especially helpful when you must maintain consistency when deleting related data stored in different tables.

Understanding Delete Join Statement

We will use the DELETE JOIN statement to delete records from multiple tables in one query. This statement lets you delete records from two or more tables connected by a relationship, such as foreign key constraints.

The general syntax for the DELETE JOIN statement is:

DELETE table1, table2
FROM table1
JOIN table2
ON table1.column1 = table2.column2
WHERE condition;

In the above syntax, you'll use table1 and table2 as the names of the tables from which you want to delete records. The JOIN clause links the two tables based on the ON condition, and the WHERE clause specifies which records to delete according to a particular condition.

Setting up Sample Data

Let's create two sample tables to demonstrate the DELETE JOIN statement.

Table 1: orders

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

Table 2: order_items

CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

The "order_items" table has a foreign key, "order_id," that refers to the "order_id" column in the "orders" table. It indicates a one-to-many relationship between the "orders" and "order_items" tables.

Populating Sample Data

With our tables ready, let's add sample data to the "orders" and "order_items" tables. Use the following SQL queries to insert the sample data:

Inserting data into the "orders" table:

INSERT INTO orders (order_id, customer_id, order_date)
VALUES
(1, 101, '2023-01-10'),
(2, 102, '2023-01-15'),
(3, 103, '2023-01-20'),
(4, 104, '2023-01-25'),
(5, 105, '2023-01-30'),
(6, 106, '2023-02-05');

Inserting data into the "order_items" table:

INSERT INTO order_items (order_item_id, order_id, product_id, quantity)
VALUES
(1, 1, 201, 2),
(2, 1, 202, 1),
(3, 2, 203, 3),
(4, 3, 204, 1),
(5, 3, 205, 2),
(6, 4, 206, 1),
(7, 5, 207, 4);

We now have many orders, such as order_id = 5 and its associated order items (order item with order_item_id = 7) and another order with order_id = 6 with no related order items.

Deleting Records From Multiple Tables

Suppose you want to delete an order with order_id = 5 and all its related order items. To do this, use the following DELETE JOIN statement:

DELETE orders, order_items
FROM orders
JOIN order_items
ON orders.order_id = order_items.order_id
WHERE orders.order_id = 5;

The above query will delete the order with order_id = 5 from the "orders" table and all related records from the order_items table.

Using LEFT JOIN or RIGHT JOIN

To delete records from one table based on records in another table, even without a match, you can use LEFT JOIN or RIGHT JOIN. For instance, to delete all orders without related order items, execute the following query:

DELETE orders, order_items
FROM orders
LEFT JOIN order_items
ON orders.order_id = order_items.order_id
WHERE order_items.order_id IS NULL;

This above query will delete records from the "orders" table where no related records are in the "order_items" table. The query will delete the order with order_id = 6 from the "orders" table since it has no related records in the order_items table.

Conclusion

In this tutorial, you learned how to delete records from multiple tables in a MySQL database using a single query. The DELETE JOIN statement is a powerful tool for maintaining data consistency across related tables while performing delete operations.