FROM (Transact-SQL)

<table_source>

Specifies a table, view, table variable, or derived table source, with or without an alias, to use in the Transact-SQL statement. Up to 256 table sources can be used in a statement, although the limit varies depending on available memory and the complexity of other expressions in the query. Individual queries may not support up to 256 table sources.

Note
Query performance may suffer with lots of tables referenced in a query. Compilation and optimization time is also affected by additional factors. These include the presence of indexes and indexed views on each <table_source> and the size of the <select_list> in the SELECT statement.

The order of table sources after the FROM keyword does not affect the result set that is returned. SQL Server returns errors when duplicate names appear in the FROM clause.

table_or_view_name

Is the name of a table or view.

If the table or view exists in another database on the same instance of SQL Server, use a fully qualified name in the form database.schema.object_name.

If the table or view exists outside the instance of SQL Serverl, use a four-part name in the form linked_server.catalog.schema.object. For more information, see sp_addlinkedserver (Transact-SQL). A four-part name that is constructed by using the OPENDATASOURCE function as the server part of the name can also be used to specify the remote table source. When OPENDATASOURCE is specified, database_name and schema_name may not apply to all data sources and is subject to the capabilities of the OLE DB provider that accesses the remote object.

[AS] table_alias

Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, SQL Server requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined.

When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.

WITH (<table_hint> )

Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement. For more information, see Table Hints (Transact-SQL).

rowset_function

Specifies one of the rowset functions, such as OPENROWSET, that returns an object that can be used instead of a table reference. For more information about a list of rowset functions, see Rowset Functions (Transact-SQL).

Using the OPENROWSET and OPENQUERY functions to specify a remote object depends on the capabilities of the OLE DB provider that accesses the object.

bulk_column_alias

Is an optional alias to replace a column name in the result set. Column aliases are allowed only in SELECT statements that use the OPENROWSET function with the BULK option. When you use bulk_column_alias, specify an alias for every table column in the same order as the columns in the file.

Note
This alias overrides the NAME attribute in the COLUMN elements of an XML format file, if present.

user_defined_function

Specifies a table-valued function.

OPENXML <openxml_clause>

Provides a rowset view over an XML document. For more information, see OPENXML (Transact-SQL).

derived_table

Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.

derived _table can use the Transact-SQL table value constructor feature to specify multiple rows. For example, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. For more information, see Table Value Constructor (Transact-SQL).

column_alias

Is an optional alias to replace a column name in the result set of the derived table. Include one column alias for each column in the select list, and enclose the complete list of column aliases in parentheses.

<tablesample_clause>

Specifies that a sample of data from the table is returned. The sample may be approximate. This clause can be used on any primary or joined table in a SELECT, UPDATE, or DELETE statement. TABLESAMPLE cannot be specified with views.

Note
When you use TABLESAMPLE against databases that are upgraded to SQL Server, the compatibility level of the database is set to 110 or higher, PIVOT is not allowed in a recursive common table expression (CTE) query. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

SYSTEM

Is an implementation-dependent sampling method specified by ISO standards. In SQL Server, this is the only sampling method available and is applied by default. SYSTEM applies a page-based sampling method in which a random set of pages from the table is chosen for the sample, and all the rows on those pages are returned as the sample subset.

sample_number

Is an exact or approximate constant numeric expression that represents the percent or number of rows. When specified with PERCENT, sample_number is implicitly converted to a float value; otherwise, it is converted to bigint. PERCENT is the default.

PERCENT

Specifies that a sample_number percent of the rows of the table should be retrieved from the table. When PERCENT is specified, SQL Server returns an approximate of the percent specified. When PERCENT is specified the sample_number expression must evaluate to a value from 0 to 100.

ROWS

Specifies that approximately sample_number of rows will be retrieved. When ROWS is specified, SQL Server returns an approximation of the number of rows specified. When ROWS is specified, the sample_number expression must evaluate to an integer value greater than zero.

REPEATABLE

Indicates that the selected sample can be returned again. When specified with the same repeat_seed value, SQL Server will return the same subset of rows as long as no changes have been made to any rows in the table. When specified with a different repeat_seed value, SQL Server will likely return some different sample of the rows in the table. The following actions to the table are considered changes: insert, update, delete, index rebuild or defragmentation, and database restore or attach.

repeat_seed

Is a constant integer expression used by SQL Server to generate a random number. repeat_seed is bigint. If repeat_seed is not specified, SQL Server assigns a value at random. For a specific repeat_seed value, the sampling result is always the same if no changes have been applied to the table. The repeat_seed expression must evaluate to an integer greater than zero.

<joined_table>

Is a result set that is the product of two or more tables. For multiple joins, use parentheses to change the natural order of the joins.

<join_type>

Specifies the type of join operation.

INNER

Specifies all matching pairs of rows are returned. Discards unmatched rows from both tables. When no join type is specified, this is the default.

FULL [ OUTER ]

Specifies that a row from either the left or right table that does not meet the join condition is included in the result set, and output columns that correspond to the other table are set to NULL. This is in addition to all rows typically returned by the INNER JOIN.

LEFT [ OUTER ]

Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

RIGHT [OUTER]

Specifies all rows from the right table not meeting the join condition are included in the result set, and output columns that correspond to the other table are set to NULL, in addition to all rows returned by the inner join.

<join_hint>

Specifies that the SQL Server query optimizer use one join hint, or execution algorithm, per join specified in the query FROM clause. For more information, see Join Hints (Transact-SQL).

JOIN

Indicates that the specified join operation should occur between the specified table sources or views.

ON <search_condition>

Specifies the condition on which the join is based. The condition can specify any predicate, although columns and comparison operators are frequently used, for example:

Transact-SQL

Copy

USE AdventureWorks2012 ;
GO
SELECT p.ProductID, v.BusinessEntityID
FROM Production.Product AS p 
JOIN Purchasing.ProductVendor AS v
ON (p.ProductID = v.ProductID);

When the condition specifies columns, the columns do not have to have the same name or same data type; however, if the data types are not the same, they must be either compatible or types that SQL Server can implicitly convert. If the data types cannot be implicitly converted, the condition must explicitly convert the data type by using the CONVERT function.

There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

For more information about search conditions and predicates, see Search Condition (Transact-SQL).

CROSS JOIN

Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

left_table_source{ CROSS | OUTER } APPLY right_table_source

Specifies that the right_table_source of the APPLY operator is evaluated against every row of the left_table_source. This functionality is useful when the right_table_source contains a table-valued function that takes column values from the left_table_source as one of its arguments.

Either CROSS or OUTER must be specified with APPLY. When CROSS is specified, no rows are produced when the right_table_source is evaluated against a specified row of the left_table_source and returns an empty result set.

When OUTER is specified, one row is produced for each row of the left_table_source even when the right_table_source evaluates against that row and returns an empty result set.

For more information, see the Remarks section.

left_table_source

Is a table source as defined in the previous argument. For more information, see the Remarks section.

right_table_source

Is a table source as defined in the previous argument. For more information, see the Remarks section.

table_source PIVOT <pivot_clause>

Specifies that the table_source is pivoted based on the pivot_column. table_source is a table or table expression. The output is a table that contains all columns of the table_source except the pivot_column and value_column. The columns of the table_source, except the pivot_column and value_column, are called the grouping columns of the pivot operator.

PIVOT performs a grouping operation on the input table with regard to the grouping columns and returns one row for each group. Additionally, the output contains one column for each value specified in the column_list that appears in the pivot_column of the input_table.

For more information, see the Remarks section that follows.

aggregate_function

Is a system or user-defined aggregate function that accepts one or more inputs. The aggregate function should be invariant to null values. An aggregate function invariant to null values does not consider null values in the group while it is evaluating the aggregate value.

The COUNT(*) system aggregate function is not allowed.

value_column

Is the value column of the PIVOT operator. When used with UNPIVOT, value_column cannot be the name of an existing column in the input table_source.

FOR pivot_column

Is the pivot column of the PIVOT operator. pivot_column must be of a type implicitly or explicitly convertible to nvarchar(). This column cannot be image or rowversion.

When UNPIVOT is used, pivot_column is the name of the output column that becomes narrowed from the table_source. There cannot be an existing column in table_source with that name.

IN (column_list )

In the PIVOT clause, lists the values in the pivot_column that will become the column names of the output table. The list cannot specify any column names that already exist in the input table_source that is being pivoted.

In the UNPIVOT clause, lists the columns in table_source that will be narrowed into a single pivot_column.

table_alias

Is the alias name of the output table. pivot_table_alias must be specified.

UNPIVOT < unpivot_clause >

Specifies that the input table is narrowed from multiple columns in column_list into a single column called pivot_column.

The FROM clause supports the SQL-92-SQL syntax for joined tables and derived tables. SQL-92 syntax provides the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS join operators.

UNION and JOIN within a FROM clause are supported within views and in derived tables and subqueries.

A self-join is a table that is joined to itself. Insert or update operations that are based on a self-join follow the order in the FROM clause.

Because SQL Server considers distribution and cardinality statistics from linked servers that provide column distribution statistics, the REMOTE join hint is not required to force evaluating a join remotely. The SQL Server query processor considers remote statistics and determines whether a remote-join strategy is appropriate. REMOTE join hint is useful for providers that do not provide column distribution statistics.

Using APPLY

Using PIVOT and UNPIVOT

Requires the permissions for the DELETE, SELECT, or UPDATE statement.

A. Using a simple FROM clause

USE AdventureWorks2012 ;
GO
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;

TerritoryID Name                          
----------- ------------------------------
1           Northwest                     
2           Northeast                     
3           Central                       
4           Southwest                     
5           Southeast                     
6           Canada                        
7           France                        
8           Germany                       
9           Australia                     
10          United Kingdom                
(10 row(s) affected)

B. Using the TABLOCK and HOLDLOCK optimizer hints

USE AdventureWorks2012 ;
GO
BEGIN TRAN
SELECT COUNT(*) 
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;

C. Using the SQL-92 CROSS JOIN syntax

USE AdventureWorks2012 ;
GO
SELECT e.BusinessEntityID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.BusinessEntityID, d.Name ;

D. Using the SQL-92 FULL OUTER JOIN syntax

USE AdventureWorks2012 ;
GO
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
WHERE p.ProductID IS NULL OR sod.ProductID IS NULL
ORDER BY p.Name ;

E. Using the SQL-92 LEFT OUTER JOIN syntax

USE AdventureWorks2012 ;
GO
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;

F. Using the SQL-92 INNER JOIN syntax

USE AdventureWorks2012 ;
GO
-- By default, SQL Server performs an INNER JOIN if only the JOIN 
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;

G. Using the SQL-92 RIGHT OUTER JOIN syntax

USE AdventureWorks2012 ;
GO
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory AS st 
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;

H. Using HASH and MERGE join hints

The following example performs a three-table join among the Product, ProductVendor, and Vendor tables to produce a list of products and their vendors. The query optimizer joins Product and ProductVendor (p and pv) by using a MERGE join. Next, the results of the Product and ProductVendor MERGE join (p and pv) are HASH joined to the Vendor table to produce (p and pv) and v.

Important
After a join hint is specified, the INNER keyword is no longer optional and must be explicitly stated for an INNER JOIN to be performed.

Transact-SQL

Copy

USE AdventureWorks2012 ;
GO
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p 
INNER MERGE JOIN Purchasing.ProductVendor AS pv 
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.BusinessEntityID = v.BusinessEntityID
ORDER BY p.Name, v.Name ;

I. Using a derived table

USE AdventureWorks2012 ;
GO
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID 
INNER JOIN
   (SELECT bea.BusinessEntityID, a.City 
    FROM Person.Address AS a
    INNER JOIN Person.BusinessEntityAddress AS bea
    ON a.AddressID = bea.AddressID) AS d
ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName, p.FirstName;

J. Using TABLESAMPLE to read data from a sample of rows in a table

USE AdventureWorks2012 ;
GO
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;

K. Using APPLY

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID) ;

L. Using PIVOT and UNPIVOT

USE AdventureWorks2012;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) AS p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY VendorID;

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE dbo.pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO dbo.pvt VALUES 
 (1,4,3,5,4,4)
,(2,4,1,5,5,5)
,(3,4,3,5,4,4)
,(4,4,2,5,5,4)
,(5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
    (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
    FROM dbo.pvt) AS p
UNPIVOT
    (Orders FOR Employee IN 
        (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO

M. Using CROSS APPLY

USE master;
GO
SELECT dbid, object_id, query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); 
GO

Reference