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 (
      SalesTotal = Sum(OrderTotal)
      dbo.CustomerOrder O
   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.

  • 2
    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

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.