MySQL in a Nutshell, 2nd Edition

Subqueries

A subquery is a SELECT statement nested within another SQL statement. This feature became available as of version 4.1 of MySQL. Although the same results can be accomplished by using the JOIN clause or UNION, depending on the situation, subqueries are a cleaner approach that is sometimes easier to read. They make a complex query more modular, which makes it easier to create and to troubleshoot. Here is a simple example of a subquery:

SELECT *
FROM 
  (SELECT col1, col2
   FROM table1
   WHERE col_id = 1000) AS derived1
ORDER BY col2;

In this example, the subquery or inner query is a SELECT statement specifying two column names. The other query is called the main or outer query. It doesn’t have to be a SELECT. It can be an INSERT, a DELETE, a DO, an UPDATE, or even a SET statement. The outer query generally can’t select data or modify data from the same table as an inner query, but this doesn’t apply if the subquery is part of a FROM clause. A subquery can return a value (a scalar), a field, multiple fields containing values, or a full results set that serves as a derived table.

You can encounter performance problems with subqueries if they are not well constructed. One problem occurs when a subquery is placed within an IN() clause as part of a WHERE clause. It’s generally better to use the = operator for each value, along with AND for each parameter/value pair.

When you see a performance problem with a subquery, try reconstructing the SQL statement with JOIN and compare the differences ...