The FROM clause: Specifying tables

The FROM clause is required in every SELECT statement involving data from tables, views, or stored procedures.

The FROM clause can include JOIN conditions linking two or more tables, and can include joins to other queries (derived tables).

Qualifying table names

In the FROM clause, the full naming syntax for tables and views is always permitted, such as:

SELECT select-list
FROM owner.table-name;

Qualifying table, view, and procedure names is necessary only when the object is owned by a user ID that is different from the user ID of the current connection, or if the user ID of the owner is not the name of a role to which the user ID of the current connection belongs.

Using correlation names

You can give a table name a correlation name to improve readability, and to save entering the full table name each place it is referenced. You assign the correlation name in the FROM clause by entering it after the table name, like this:

SELECT d.DepartmentID, d.DepartmentName
FROM Departments d;

When a correlation name is used, all other references to the table, for example in a WHERE clause, must use the correlation name, rather than the table name. Correlation names must conform to the rules for valid identifiers.

Querying derived tables

A derived table is a table derived directly, or indirectly, from one or more tables by the evaluation of a query expression. Derived tables are defined in the FROM clause of a SELECT statement.

Querying a derived table works the same as querying a view. That is, the values of a derived table are determined at the time the derived table definition is evaluated. Derived tables differ from views, however, in that the definition for a derived table is not stored in the database. Derived tables differ from base and temporary tables in that they are not materialized and they cannot be referred to from outside the query in which they are defined.

The following query uses a derived table (my_derived_table) to hold the maximum salary in each department. The data in the derived table is then joined to the Employees table to get the surnames of the employee earning the salaries.

SELECT Surname, 
   my_derived_table.maximum_salary AS Salary, 
   my_derived_table.DepartmentID 
FROM Employees e, 
  ( SELECT MAX( Salary ) AS maximum_salary, DepartmentID 
      FROM Employees 
      GROUP BY DepartmentID ) my_derived_table
  WHERE e.Salary = my_derived_table.maximum_salary 
  AND e.DepartmentID = my_derived_table.DepartmentID
ORDER BY Salary DESC;

Surname	Salary	DepartmentID
Shea	138948.00	300
Scott	96300.00	100
Kelly	87500.00	200
Evans	68940.00	400
Martinez	55500.80	500

The following example creates a derived table (MyDerivedTable) that ranks the items in the Products table, and then queries the derived table to return the three least expensive items:

SELECT TOP 3 *
       FROM ( SELECT Description, 
                     Quantity, 
                     UnitPrice,
                     RANK() OVER ( ORDER BY UnitPrice ASC ) 
                 AS Rank 
                 FROM Products ) AS MyDerivedTable
ORDER BY Rank;

Querying objects other than tables

The most common elements in a FROM clause are table names. However, it is also possible to query rows from other database objects that have a table-like structure—that is, a well-defined set of rows and columns. For example, you can query views, or query stored procedures that return result sets.

For example, the following statement queries the result set of a stored procedure called ShowCustomerProducts.

SELECT *
FROM ShowCustomerProducts( 149 );