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

From WikiContent

(Difference between revisions)
Jump to: navigation, search
(New page: If you were to read a definition of the FROM clause in many of the introductory SQL books, you might see something like the following: The FROM clause identifies one or more tables from w...)
Current revision (19:35, 22 January 2010) (edit) (undo)
 
(One intermediate revision not shown.)
Line 1: Line 1:
-
If you were to read a definition of the FROM clause in many of the introductory SQL books, you might see something like the following:
+
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
+
''The FROM clause identifies one or more tables from which data is to be retrieved''
-
While this 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 of any of the following types:
+
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
+
* Tables
-
Views
+
* Views
-
Subqueries
+
* Subqueries
-
Files
+
* Files
-
XML Documents
+
* XML Documents
-
Functions
+
* Functions
-
Did you know that you can write a query that will read data from a file on the server? Or interact with the results of another query as if it were a table? Or, my favorite, process the results of a function call which returns a result set? If not, then you owe it to yourself to find out all the ways in which your database server can interact with various sources, whether they be managed locally or remotely.
+
By branching out from the simple definition of the FROM clause, therefore, you will be able to write queries which:
-
So, a far better definition would be:
+
* 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)
-
The FROM clause identifies one or more data sources from which data is to be retrieved
+
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.

Current revision

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