User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Joins involving derived tables

You can nest queries within a FROM clause. Tables created in this manner are called derived tables. Using derived queries, you can perform grouping of groups or construct a join with a group, without having to create a view.

In the following example, the inner SELECT statement (enclosed in parentheses) creates a derived table, grouped by customer id values. The outer SELECT statement assigns this table the correlation name sales_order_counts and joins it to the customer table using a join condition.

SELECT lname, fname, number_of_orders
FROM customer join
   (   SELECT cust_id, count(*)
      FROM sales_order
      GROUP BY cust_id   )
   AS sales_order_counts (cust_id, number_of_orders)
   ON (customer.id = sales_order_counts.cust_id)
WHERE number_of_orders > 3

The result is a table of the names of those customers who have placed more than three orders, including the number of orders each has placed.