• Article

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:

    USE AdventureWorks2008R2;
GO
SELECT *
FROM Sales.SalesOrderHeader

  • Joins between two or more tables or views:

    USE AdventureWorks2008R2;
GO
SELECT Cst.CustomerID, Sord.OrderQty, Ord.ShipDate, Ord.Freight 
FROM AdventureWorks2008R2.Sales.Customer AS Cst
JOIN AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord
   ON Cst.CustomerID = Ord.CustomerID
JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS Sord
   ON Sord.SalesOrderID = Ord.SalesOrderID;

  • 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 AdventureWorks2008R2 ;
GO
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name,
 d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e 
   ON p.BusinessEntityID = e.BusinessEntityID 
INNER JOIN Person.BusinessEntityAddress AS bea 
   ON e.BusinessEntityID = bea.BusinessEntityID 
INNER JOIN (SELECT AddressID, City FROM Person.Address) AS d
   ON bea.AddressID = d.AddressID

  • ORDER BY p.LastName, p.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 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('AdventureWorks2008R2')