Tables Are Just One of the Things You Can Put in a FROM Clause

From WikiContent

Revision as of 19:35, 22 January 2010 by Albeau (Talk | contribs)
(diff) ←Older revision | Current revision (diff) | Newer revision→ (diff)
Jump to: navigation, search

Pity the poor FROM clause. He's the 98-pound weakling of the SQL clauses, after all, whose role is oftened summed up in introductory SQL books with a single sentence, such as:

The FROM clause identifies one or more tables from which data is to be retrieved

If that were my designated role in life, I'd consider a career change (human speed-bump comes to mind...). While the above definition is certainly true and covers a large percentage of use cases, it is far from being a comprehensive definition. Depending on which database server you are using, the elements referenced in your FROM clause may be any of the following types:

  • Tables
  • Views
  • Subqueries
  • Files
  • XML Documents
  • Functions

By branching out from the simple definition of the FROM clause, therefore, you will be able to write queries which:

  • open and read data from a file on the server
  • interact with the results of another query as if it were a table
  • parse an XML document
  • call a function which returns a result set (my personal favorite)

It looks like the FROM clause has been hitting the gym, doesn't it?

Challenge yourself to find out all the ways in which your database server can interact with various sources, whether they are managed locally or remotely. You might gain a new respect for the FROM clause, and write some elegant code in the process.

Personal tools