Subqueries in the FROM clause

This topic describes subqueries that occur as nested SELECT statements in the FROM clause of an outer SELECT statement. Such subqueries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.

The following query uses asterisk notation in the outer query to return the results of a subquery that retrieves all fields of the address column in the employee table.

Figure 1. Query

SELECT * FROM (SELECT address.* FROM employee);

Figure 2. Query result

address   ROW(102 Ruby, Belmont, CA, 49932, 1000)
address   ROW(133 First, San Jose, CA, 85744, 4900)
address   ROW(152 Topaz, Willits, CA, 69445, 1000))

This illustrates how to specify a derived table, but it is a trivial example of this syntax, because the outer query does not manipulate any values in the table expression that the subquery in the FROM clause returns. (See Query for a simple query that returns the same results.)

The following query is a more complex example in which the outer query selects only the first qualifying row of a derived table that a subquery in the FROM clause specifies as a simple join on the customer and cust_calls tables.

Figure 3. Query

SELECT LIMIT 1 * FROM 
   (SELECT c.customer_num, c.lname, c.company, 
           c.phone, u.call_dtime, u.call_descr
                 FROM customer c, cust_calls u
                 WHERE c.customer_num = u.customer_num
    ORDER BY u.call_dtime DESC);

The query returns only those rows in which the customer has made a call to customer service, as the result shows.

Figure 4. Query result

customer_num  106
lname         Watson
company       Watson & Son
phone         415-389-8789
call_dtime    1998-06-12 08:20
call_descr    Order was received, but two of the cans of 
              ANZ tennis balls within the case were empty

In the preceding example, the subquery includes an ORDER BY clause that specifies a column that appears in Projection list of the subquery, but the query would also be valid if the Projection list had omitted the u.call_dtime column. The FROM clause is the only context in which a subquery can specify the ORDER BY clause.