The From Clause and SQL Queries - The from Clause

(Page 2 of 4 )

Thus far, you have seen queries whose from clauses contain a single table. Although most SQL books will define the from clause as simply a list of one or more tables, I would like to broaden the definition as follows:

Thefromclause defines the tables used by a query, along with the means of linking the tables together.

This definition is composed of two separate but related concepts, which will be explored in the following sections.

Tables

When confronted with the term table, most people think of a set of related rows stored in a database. While this does describe one type of table, I would like to use the word in a more general way by removing any notion of how the data might be stored and concentrating on just the set of related rows. There are three different types of tables that meet this relaxed definition:

1. Permanent tables (i.e., created using thecreate table statement)
   
2. Temporary tables (i.e., rows returned by a subquery)
   
3. Virtual tables (i.e., created using thecreate viewstatement)

Each of these table types may be included in a query’sfromclause. By now, you should be comfortable with including a permanent table in afromclause, so I will briefly describe the other types of tables that can be referenced in afromclause.

Subquery-generated tables

A subquery is a query contained within another query. Subqueries are surrounded by parentheses and can be found in various parts of a select statement; within thefromclause, however, a subquery serves the role of generating a temporary table that is visible from all other query clauses and can interact with other tables named in thefromclause. Here’s a simple example:

  mysql> SELECT e.emp_id, e.fname, e.lname
      -> FROM (SELECT emp_id, fname, lname, start_date, title
      ->   FROM employee) e;

  +--------+----------+------------+
  | emp_id | fname    | lname      |
  +--------+----------+------------+
  |      1 | Michael  | Smith      |
  |      2 | Susan    | Barker     |
  |      3 | Robert   | Tyler      |
  |      4 | Susan    | Hawthorne  |
  |      5 | John     | Gooding    |
  |      6 | Helen    | Fleming    |
  |      7 | Chris    | Tucker     |
  |      8 | Sarah    | Parker     |
  |      9 | Jane     | Grossman   |
  |     10 | Paula    | Roberts    |
  |     11 | Thomas   | Ziegler    |
  |     12 | Samantha | Jameson    |
  |     13 | John     | Blake      |
  |     14 | Cindy    | Mason      |
  |     15 | Frank    | Portman    |
  |     16 | Theresa  | Markham    |
  |     17 | Beth     | Fowler     |
  |     18 | Rick     | Tulman     |
  +--------+----------+------------+
  18 rows in set (0.00 sec)

In this example, a subquery against theemployee table returns five columns, and the containing query references three of the five available columns. The subquery is referenced by the containing query via its alias, which, in this case, ise. This is a simplistic and not-particularly-useful example of a subquery in afromclause; you will find complete coverage of subqueries in Chapter 9.

Next: Views >>
 

More Database Articles Articles
More By O'Reilly Media

Comments On: The From Clause and SQL Queries

· This article is an excerpt from the book "Learning SQL," published by O'Reilly. We...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.