DELETE with Subquery

Subqueries inside DELETE statements let you filter rows based on complex conditions derived from other tables or aggregate computations. This avoids the need for temporary tables in many scenarios.

Using IN with a Subquery

The most common pattern is using WHERE column IN (SELECT ...) to delete rows whose key appears in the result set of another query. This is readable and flexible.

MySQL Limitation

MySQL does not allow you to reference the table you are deleting from in the subquery directly. The workaround is to wrap the subquery in a derived table (an inner SELECT aliased as a temporary name). This is a well-known MySQL quirk.

Use IN (SELECT ...) for list-based filtering
Use EXISTS (SELECT 1 FROM ... WHERE ...) for correlated checks
Wrap self-referencing subqueries in a derived table

Example

-- Delete products that have never been ordered
DELETE FROM products
WHERE product_id NOT IN (
    SELECT product_id
    FROM (
        SELECT DISTINCT product_id FROM order_items
    ) AS ordered
);

SQL

Full Editor

Pro Tip

Wrap subqueries that reference the same table in a derived table alias to bypass MySQL's restriction.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned