I have
previously described how to use subqueries to connect two tables. You can also
accomplish the same thing with derived tables. These are where you issue a
query, surround it with parentheses, and assign an alias to the result. Then
the results behave just like a table that can be joined with an outer table.
The reason to use a derived table is that it
might, in some circumstances, be faster than using a join. The simplest of
derived tables have a stand alone query you can independently run. But that is
not a requirement. The SQL for the derived table can reference columns in the
outer query. This is called a correlated subquery, and can provide a way to tie
results between the two tables.
