Subqueries
The WHERE clause reduces the amount of data through a simple filtering process and HAVING filters the results. But MySQL also provides more robust ways of narrowing the data from which results are culled. Normally, MySQL processes a query against a database that is resident on disk. Subqueries, however, are nested SELECT statements that result in a table of results against which the main query is processed. Once the main query is processed against the results of the subquery, the latter is purged from memory.
Up to this point, if we needed to take the results from one query and use it as input for another query, we might feel constrained to use two SELECT statements and manually transfer the data. Here, however, subqueries do that for us.
For example, let's say that we wanted to find the title of every movie done by actors with the surname CHASE. The sakila database does not provide this information in one table. Using a series of SELECT statements, we would first need to retrieve...
