The most prominent new feature of MySQL 4.1 is the introduction of subqueries (also known as subselects), a very welcome addition which brings MySQL's feature set a little closer to that of other databases.

A little experimentation shows that support is fairly complete, with nestable subqueries supported in all applicable sections of a query. Derived tables are also now available.

One caveat is that it is not possible to use a subquery which references a table which is modified in the same query. This means statements like this:

 DELETE FROM t1
  WHERE id IN (SELECT MIN(id) FROM t1)
      

will fail with an error like ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause which means statements using the non-standard multiple-table DELETE syntax introduced MySQL 4.0.0 cannot be rewritten to use subqueries.

Futher reference

• http://dev.mysql.com/doc/mysql/en/Subqueries.html
• Article at dev.mysql.com: Nesting SELECTs in MySQL 4.1