SQL: Derived Tables

A derived table is a SQL construct consisting of a SELECT statement embedded in the FROM clause of another SELECT statement. Derived table support is required for full ANSI-92 SQL compliance. Derived tables are referred to by a variety of names, including: table subqueries, nested queries, and table value constructors (the formal ANSI-92 SQL name).

Derived tables let you develop queries in a modular way. This means that using them is quite similar to using views, but there are obvious advantages for one-off queries. They also can remove duplication and make queries easier to read.

The following simple example illustrates the key points for derived tables. 

-- Count the days on which orders are placed.
SELECT
  DAYOFWEEK(o_orderdate),
  COUNT(*)
FROM ordertab
GROUP BY 1
ORDER BY 1;

-- Same thing, but just for work days.
SELECT
  DAYOFWEEK(o_orderdate),
  COUNT(*)
FROM ordertab
WHERE DAYOFWEEK(o_orderdate) BETWEEN 1 AND 5
GROUP BY 1
ORDER BY 2 DESC;

-- Using a derived table makes this neater and easier to modify.
SELECT *
FROM (
  SELECT
    DAYOFWEEK(o_orderdate),
    COUNT(*)
  FROM ordertab
  GROUP BY 1
  ) AS orders(dayoftheweek, ordercount)
WHERE dayoftheweek BETWEEN 1 AND 5
ORDER BY 2 DESC;

In the above example both the derived table and its columns have aliases. However, the SQL syntax only requires that the table is given an alias as the column names are inherited from the underlying base table(s).