ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Specialized joins

Joins involving derived tables

Derived tables allow you to nest queries within a FROM clause. With derived tables, you can perform grouping of groups, or you can 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.

For an explanation of key joins of derived tables, see Key joins of views and derived tables.

For an explanation of natural joins of derived tables, see Natural joins of views and derived tables.

For an explanation of outer joins of derived tables, see Outer joins of views and derived tables.