Why Does MySQL Fail When Using LIMIT in NOT IN Subqueries?

LIMIT in NOT IN Subqueries: MySQL Limitations

When working with MySQL, you may encounter limitations when using subqueries within LIMIT clauses. One common issue arises when attempting to delete records that are not included in a specific range.

Suppose you want to delete posts that are not among the latest 15. A naive approach might involve the following query:

DELETE FROM posts 
WHERE id NOT IN 
(SELECT id FROM posts ORDER BY timestamp DESC LIMIT 0, 15);

However, this query will likely fail, returning an error message such as "MySQL doesn't yet support LIMIT & IN/ALL/ANY/SOME subquery."

Resolving the Limitation

To overcome this limitation, you can employ a workaround by enclosing the subquery within another subquery:

DELETE FROM posts 
WHERE id NOT IN 
(SELECT * FROM (
    SELECT id 
    FROM posts 
    ORDER BY timestamp DESC 
    LIMIT 0, 15
) AS t);

In this modified query, the subquery that selects the latest 15 posts is assigned an alias, "t," and then the outer subquery uses the alias to retrieve the excluded IDs. The result is a valid query that should achieve the desired deletion.

The above is the detailed content of Why Does MySQL Fail When Using LIMIT in NOT IN Subqueries?. For more information, please follow other related articles on the PHP Chinese website!