Are view references in a query properly called "derived tables" as such?

Question

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;

The 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.

I can't find that comment on Microsoft and Oracle in the final specs (not the draft) - although I do agree that a lot of people do mix up "sub-queries" and "derived tables" - but that is not your question. The specs states later in that chapter: "A view is a named query, which can be invoked by use of this name. The result of such an invocation is called a viewed table" — , Feb 12 '13 at 19:46

score 3 · Answer 1 · edited Apr 13 '17 at 12:43

3

From a SQL Server perspective, a table expression is a named query. This concept of a named query seems in line with what @a_horse_with_no_name commented on earlier. There are four types of of table expressions which are:

Derived Tables
Common Table Expressions
Views
Inline Table-Value Functions

A Derived Table is a subquery that returns an entire table result. A CTE is like a derived table but usually thought to be more readable and modular. It also has a recursive feature. A View is a reusable named query in which the definition of the table expression is stored. The primary difference between a View and an Inline Table-Valued Function is that the latter takes parameters whereas the former does not.

^{This has been compiled from the discussion on Table Expressions in Ben-Gan, et al, Querying Microsoft SQL Server 2012, Chapter 4.}

edited Apr 13 '17 at 12:43

Community

1

answered Feb 13 '13 at 02:10

swasheck

10,665
4
47
88

Do you have thoughts on the difference between the definition of a "named query" and a reference to a named query? – ErikE Feb 13 '13 at 17:14
@ErikE I'm not entirely sure I understand what you're driving at. I understand the difference, but help me understand what the thoughts would address. – swasheck Feb 13 '13 at 22:49
I guess I just wanted to know the "official" stance: would a true derived table explicated in a query always be introduced with parentheses, or would simply referring to the name of a view (itself a type of "derived table" or "named query") also be properly called "introducing a derived table to the query"? I see a difference between having the actual query contents present, or having them hidden behind a layer of abstraction (behind a name). – ErikE Feb 13 '13 at 23:40
I'm not sure there is a difference, at least not in the minds of the authors of the book I cited. The "naming" of the table expression allows for reuse outside of the context of that batch. – swasheck Feb 13 '13 at 23:58

Are view references in a query properly called "derived tables" as such?

1 Answers1