DELETE with Subqueries in MySQL

When working with a MySQL database, you may need to delete a row based on a specific condition to improve performance and ensure the database remains clean and optimized. At that time, the DELETE statement is used to delete records from a table. But when dealing with complex datasets, DELETE with Subqueries helps you delete rows more efficiently and dynamically. In this article, you will learn what subqueries are and how delete statements use subqueries to delete rows efficiently. 

Table of Contents: 

• What is a Subquery in MySQL?
• Why Do You Need To Use Subqueries with DELETE?
• Types of Subqueries in SQL
  1. DELETE with Scalar Subquery in MySQL
  2. DELETE with Multi-Row Subquery in MySQL
• DELETE with JOIN in MySQL
• Performance Comparison
• Limitations of DELETE with Subqueries in MySQL
• Real-World Examples
• Conclusion

What is a Subquery in MySQL?

A subquery in MySQL is a query that is nested inside another query. The subquery will work with the WHERE clause to choose which row to delete. The subquery can also be inside the SELECT, INSERT, UPDATE, or DELETE statements. Here, let’s learn about the DELETE with subquery in MySQL. But MySQL will not let you update or delete the table that you are working on currently; For this, you can use a derived table.

General Syntax of a Subquery: 

SELECT column1
FROM table1
WHERE column2 = (
    SELECT column2
    FROM table2
    WHERE condition
);

DELETE with Subquery Syntax:

DELETE FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM another_table
    WHERE condition
);

Why Do You Need To Use Subqueries with DELETE?

The subqueries will allow you to filter the data from other tables in a database. This will make it easier to delete rows dynamically in complex queries. This will maintain the readability and reduce the complexity. 

Master SQL: Empower Your Data Skills Today!

Unlock the power of databases with hands-on SQL training and become a data-driven professional

Explore Program

Types of Subqueries in SQL

There are four types of subqueries in SQL, like scalar subqueries and multi-row subqueries. Let’s create a dataset to perform delete with subquery operations. 

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);
INSERT INTO Customers VALUES
(1, 'Amit Sharma', '[email protected]'),
(2, 'Priya Verma', '[email protected]'),
(3, 'Ravi Kumar', '[email protected]'),
(4, 'Neha', '[email protected]');
SELECT * FROM Customers;

This is how the table looks after creation. 

1. DELETE with Scalar Subquery in MySQL

A scalar subquery will return only one value. It is usually used with SELECT, WHERE, or SET results. This can be used when you want to delete a single row or column in a table. 

Example:

DELETE FROM Customers
WHERE customer_id = (
    SELECT max_id FROM (
        SELECT MAX(customer_id) AS max_id FROM Customers
    ) AS temp
);
SELECT * FROM Customers;

Output:

Explanation: Here, the scalar subquery deleted customer_id 4 without mentioning it directly.

2. DELETE with Multi-Row Subquery in MySQL

A multi-row subquery in MySQL will return more than one row. It usually uses operators like IN, ANY, or ALL to get the desired result. 

Example:

CREATE TABLE Blacklist (
    email VARCHAR(100)
);
INSERT INTO Blacklist VALUES
('[email protected]'),
('[email protected]');
DELETE FROM Customers
WHERE email IN (
    SELECT email FROM Blacklist
);
SELECT * FROM Customers;

Output:

Explanation: Here, the multi-row subquery queried the two tables and deleted the emails from the blacklist. 

DELETE with JOIN in MySQL

The DELETE with JOINS will work on certain databases like PostgreSQL, MySQL and SQL Server. The MySQL database has certain restrictions that prevent the user from deleting foreign key references. This doesn’t follow the standard of the ANSI model. 

Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);
INSERT INTO Customers (customer_id, name, email) VALUES
(1, 'Amit Sharma', '[email protected]'),
(2, 'Priya Verma', '[email protected]'),
(3, 'Ravi Kumar', '[email protected]'),
(4, 'Neha', '[email protected]');
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
INSERT INTO Orders (order_id, customer_id, order_date) VALUES
(1, 1, '2015-04-21'),
(2, 2, '2021-12-02'),
(3, 3, '2019-03-03');
DELETE o
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.customer_id = 1;
SELECT * FROM Orders

Output:

Explanation: Here, the DELETE with JOINS deleted the customer ID 1 from the table. 

Performance Comparison

Limitations of DELETE with Subqueries in MySQL

1. In MySQL, you cannot reference the same table that you are currently working on. So while working on MySQL, make sure that the table you are working on is not referencing the table you want to be modified, like DELETE, or use a derived table to edit.
2. Using NOT IN in MySQL will reduce the performance while working on large databases. Instead, use JOINS for better performance. 
3. There shouldn’t be any NULL records in the table if you plan to use NOT IN, as it may lead to unexpected errors in the database. Use IS NOT NULL along with NOT IN to avoid unexpected behavior. 
4. MySQL does not allow modifying a table that is also used in a subquery of the same DELETE statement unless a derived table is used. The DELETE with subqueries is very readable when working on small datasets, but if it involves complex subqueries, it will become hard to interpret or maintain. 
5. DELETE with subqueries in MySQL will not support ANSI SQL standards, as some operations are not portable from one database to another. 
6. DELETE with subqueries is less flexible when we delete multiple rows together. Using DELETE with JOINS is more flexible and straightforward. 

Real-World Examples

Some real-world examples use DELETE with Subqueries or JOINS in MySQL to delete the unwanted records. 

Case 1: To delete the record of the person who borrowed books 

CREATE TABLE Members (
    member_id INT PRIMARY KEY,
    name VARCHAR(100),
    joined_date DATE
);
INSERT INTO Members (member_id, name, joined_date) VALUES
(1, 'Arun', '2022-04-17'),
(2, 'Deepak', '2023-08-10'),
(3, 'Kiran', '2024-12-13'),
(4, 'Ajith', '2022-02-19');
CREATE TABLE BorrowedBooks (
    borrow_id INT PRIMARY KEY,
    member_id INT,
    borrowed_date DATE,
    FOREIGN KEY (member_id) REFERENCES Members(member_id)
);
INSERT INTO BorrowedBooks (borrow_id, member_id, borrowed_date) VALUES
(101, 1, '2023-01-01'),
(102, 2, '2024-12-01'),
(103, 3, '2025-03-01');
DELETE bb
FROM BorrowedBooks bb
JOIN Members m ON bb.member_id = m.member_id
WHERE m.member_id = 2;
DELETE FROM Members
WHERE member_id = 2;
SELECT * FROM Members

Output: 

Explanation: Here, the JOIN Statement joined the member ID and member name from the member table, and then the DELETE statement deleted the record of member ID 2 from the table. 

Case 2: Using the DELETE statement to delete an author along with the book details. 

CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
);
INSERT INTO Authors (author_id, name) VALUES
(1, 'Charan'),
(2, 'Jafar'),
(3, 'Praveen');
CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);
INSERT INTO Books (book_id, title, author_id) VALUES
(101, 'Abc', 1),
(102, 'Mostly', 2),
(103, 'Anonymous', 3),
(104, 'Gadar', 3);
DELETE b
FROM Books b
JOIN Authors a ON b.author_id = a.author_id
WHERE a.author_id = 3;
DELETE FROM Authors
WHERE author_id = 3;
SELECT * FROM Books;

Output: 

Explanation: Here, the JOIN Statement joined the books and author table for their author ID, and then the DELETE statement deleted the author who has ID number 3. 

Kickstart Your SQL Journey – For Free!

Learn how to write powerful queries and manage databases with our beginner-friendly free SQL course.

Explore Program

Conclusion

DELETE with subqueries in MySQL is used to filter and remove specific records or rows from the tables. It is efficient when you want to work on smaller datasets. It has high readability and maintainability on smaller datasets, but may have slower performance when you need to work on large datasets. Subqueries can handle NULL, but if there is a NOT IN function present, then it cannot accept the NULL values and may produce unexpected errors. If you use JOINS instead, it may help you to work on NULLs as well as large datasets. DELETE with JOINs has better performance and readability with complex queries. In this article, you have learned about subqueries and DELETE with subqueries and alternative methods like JOINs. Use the correct method based on the type of application you are working with. 

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

DELETE with Subqueries in MySQL – FAQs