The next type of subquery uses what is referred to as a derived table, appearing in the list above as “a table to select from.” In this usage, the subquery expression appears as a part of the FROM clause or to test membership. Subqueries used to test membership will be used with keywords like ANY, EXISTS, and NOT EXISTS in the outer query and may or may not be replaceable by a join. On the flip side are tables to select from, which are easily replaced with joins in nearly any situation. The tables created by these subqueries are called derived tables, which are essentially unnamed, non-persistent views.

Testing membership is one of the core features that any implementation of SQL should provide. SQL is a set oriented language and memberhip tests are fundamental in set theory. While MySQL has provided the UNION operation for as long as I can remember, the EXISTS, NOT EXISTS, and ANY operations are new and much needed. Subqueries that test membership can also be correlated and are especially useful when performing complex reporting operations. In the following example, we will select values from one table where they do not exist in another.

SELECT 
    column1
FROM 
    table 1
WHERE 
    NOT EXISTS
        (
         SELECT
              column1 
         FROM
              table2 
        )

In this example, the inner query is executed first, selecting column1 from table2. The result of that subquery is used in the NOT EXISTS clause which, logically, is the same as using NOT IN and declaring a set of values by hand. In fact, the EXISTS and IN statements can be used interchangeably in this context.

An example of a derived table in the FROM clause is shown below.

SELECT
     column1 
FROM
     (
      SELECT
           column1
      FROM
           table2
     )

The process here is similar to that of a correlated subquery – the engine first processes the inner query, creating a derived table. That derived table is then used in the outer query as the target for record selection. These queries can also be correlated and are typically more useful in that context, but do have uses on their own.