FROM by George Hernandez

The syntax of the FROM clause is as follows:

FROM TableSources

The FROM clause is used in three of the four fundamental SQL commands for data manipulation:

The FROM clause specifies other table or view sources to be utilized in the search condition of the WHERE clause of a DELETE command, i.e. no rows are deleted from TableSources.
The FROM clause specifies other table or view sources from which rows are pulled to make the results of a SELECT command.
The FROM clause specifies other table or view sources to be utilized in the search condition of the WHERE clause of a UPDATE command, i.e. no rows are updated from TableSources.

TableSources is a comma-separated list of tables, names and JOIN clauses used by command in which the FROM clause exists.

The table-like items in TableSources can be aliased with the AS keyword. Once aliased, the table-like items can only be referred to with the alias. This applies especially when using the table to qualify columns. The syntax is as follows:

FROM table [AS] TableAlias [, ...]

EGs

Simple FROM

The most common FROM clause usage is to specify a single table.

SELECT *
FROM tblA

FROM with JOIN

This FROM clause specifies 2 tables joined together to act as one.

DELETE tblA
FROM tblA INNER JOIN tblB
     ON tblB.Name = tblA.Name
WHERE tblA.Name = 'George'

FROM with sub-query

This FROM clause specifies a table derived from a sub-query.

SELECT ST.stor_id, ST.stor_name
FROM stores AS ST
     INNER JOIN
     (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
      FROM sales
      GROUP BY stor_id)
      AS SA
     ON ST.stor_id = SA.stor_id
WHERE SA.title_count = (SELECT COUNT(*) FROM titles)

Page Modified: (Hand noted: 2007-10-12 19:48:13Z) (Auto noted: 2007-11-17 06:45:15Z)