Using the FROM Clause

The FROM clause is required in every SELECT statement in which data is being retrieved from tables or views. Use the FROM clause to:

• List the tables and views containing the columns referenced in the select list and in the WHERE clause. The table or view names can be aliased using the AS clause.
• Join types. These are qualified by join conditions specified in the ON clause.

The FROM clause is a comma-separated list of table names, view names, and JOIN clauses.

Transact-SQL has extensions that support the specification of objects other than tables or views in the FROM clause. These other objects return a result set, or rowset in OLE DB terms, that form a virtual table. The SELECT statement then operates as if the result set were a table.

The FROM clause can specify:

• One or more tables or views, for example:

  SELECT *

  FROM Shippers

    

• Joins between two tables or views:

  SELECT Cst.CustomerID, Cst.CompanyName, Cst.ContactName,

         Ord.ShippedDate, Ord.Freight

  FROM Northwind.dbo.Orders AS Ord

    JOIN

       Northwind.dbo.Customers AS Cst

    ON (Cst.CustomerID = Ord.CustomerID)

    

• One or more derived tables, which are SELECT statements in the FROM clause referred to by an alias or a user-specified name. The result set of the SELECT in the FROM clause forms a table used by the outer SELECT statement. For example, this SELECT uses a derived table to find if any store carries all book titles in the pubs database:

  SELECT ST.stor_id, ST.stor_name

  FROM stores AS ST,

       (SELECT stor_id, COUNT(DISTINCT title_id) AS title_count

        FROM sales

        GROUP BY stor_id

       ) AS SA

  WHERE ST.stor_id = SA.stor_id

    AND SA.title_count = (SELECT COUNT(*) FROM titles)

    

• One or more tables or views from a linked server defined using sp_addlinkedserver. A linked server can be any OLE DB data source.
• An OLE DB rowset returned by either the OPENROWSET or OPENQUERY functions.

The basis of Microsoft® SQL Server™ distributed queries are linked servers, OPENROWSET, and OPENQUERY. They provide the ability to query or modify data in any OLE DB data source as a part of Transact-SQL statements.

SELECT Statements Without FROM Clauses

The SELECT statements that do not require a FROM clause are those that are not selecting data from any tables in the database. These SELECT statements only select data from local variables or Transact-SQL functions that do not operate on a column, for example:

SELECT @MyIntVariable

SELECT @@VERSION

SELECT DB_ID('Northwind')

See Also