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™ 2000 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

Distributed Queries

OPENQUERY

FROM

Using Joins

OPENROWSET