While answering a question on stackoverflow, I presented a definition of derived tables:
A derived table is a complete query, inside of parentheses, that is used as if it were a real table.
But a commenter objected:
Though there are other kinds of derived tables besides those "inside of parentheses". ... [such as] Views and Table-Valued Functions ... .
and further backed this up with:
From the ISO/IEC 2003 Spec, section 4.3 of the Framework volume, page 13 of the August 2003 draft Spec: "An operation that references zero or more base tables and returns a table is called a query. The result of a query is called a derived table." Note that both Views and Table-Valued Functions return "the result of a query", which is a derived table. Microsoft (and to a lesser extent, Oracle) are notorious for mistakenly equating "derived table" and "sub-query" in their documentation, but Derived Tables do also include pre-defined queries like Views.
So what's the real scoop, here? Am I to relegate what I thought of as derived tables to simply "FROM clause aliased inline subqueries" or are views not properly derived tables?
Please note: I searched for quite a while online and could not find anything definitive. I don't have a copy of the said spec.
Also, I think it's worth addressing something else. Let's say that views are properly called "derived tables". Does this make the reference to the view also a "derived table" or merely a reference? For an example with a CTE that should drive the point home:
WITH SalesTotals AS ( SELECT O.CustomerID, SalesTotal = Sum(OrderTotal) FROM dbo.CustomerOrder O GROUP BY O.CustomerID ) SELECT C.Name, S.SalesTotal FROM dbo.Customer C INNER JOIN SalesTotals S ON C.CustomerID = S.CustomerID;
SalesTotals CTE introduced with
WITH is a derived table. But is
INNER JOIN SalesTotals also a derived table, or just a reference to a derived table? Does this query have two derived tables or one? If one, then by extension I think that a view may be a derived table, but referencing it may not have to be a derived table.