Using the FROM Clause

Article
12/03/2008

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 SalesOrderHeader
```

Joins between two or more tables or views:

SELECT Cst.CustomerID, St.Name, Ord.ShipDate, Ord.Freight 
FROM AdventureWorks.Sales.Store AS St
JOIN AdventureWorks.Sales.Customer AS Cst
ON St.CustomerID = Cst.CustomerID
JOIN AdventureWorks.Sales.SalesOrderHeader AS Ord
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, the following SELECT uses a derived table to return the city in which each employee lives:
```
USE AdventureWorks ;
GO
SELECT RTRIM(c.FirstName) + ' ' + LTRIM(c.LastName) AS Name,
 d.City
FROM Person.Contact c
INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID 
INNER JOIN (SELECT AddressID, City FROM Person.Address) AS d
ON e.AddressID = d.AddressID
ORDER BY c.LastName, c.FirstName ;
```
In addition to joins, the APPLY operator can be used in the FROM clause to evaluate the right-hand-side input, which is typically a table-valued function, against every row of the left-hand-side input table and merge the results from all these evaluations. For more information, see Using APPLY.
The PIVOT and UNPIVOT operators can be used in the FROM clause for reshaping the input table. The PIVOT operator generates new columns in the output based on values in columns from its input. For more information, see Using PIVOT and UNPIVOT.
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 2005 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('AdventureWorks')

Using the FROM Clause

SELECT Statements Without FROM Clauses

See Also

Concepts

Other Resources

Help and Information

Additional resources