FROM

本主题适用于： SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

在SQL Server 2016中，指定用于DELETE语句、SELECT语句以及UPDATE语句的表、视图、派生表以及交联表。在SELECT语句中，FROM子句是必不可少的，除非select列表只包含常量、变量以及算术表达式（无列名）。

句法

-- Syntax for SQL Server and Azure SQL Database  
[ FROM { <table_source> } [ ,...n ] ]   
<table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ <tablesample_clause> ]   
        [ WITH ( < table_hint > [ [ , ]...n ] ) ]   
    | rowset_function [ [ AS ] table_alias ]   
        [ ( bulk_column_alias [ ,...n ] ) ]   
    | user_defined_function [ [ AS ] table_alias ]  
    | OPENXML <openxml_clause>   
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]   
    | <joined_table>   
    | <pivoted_table>   
    | <unpivoted_table>  
    | @variable [ [ AS ] table_alias ]  
    | @variable.function_call ( expression [ ,...n ] )   
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]  
    | FOR SYSTEM_TIME <system_time>   
}  
<tablesample_clause> ::=  
    TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )   
        [ REPEATABLE ( repeat_seed ) ]   
<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON <search_condition>   
    | <table_source> CROSS JOIN <table_source>   
    | left_table_source { CROSS | OUTER } APPLY right_table_source   
    | [ ( ] <joined_table> [ ) ]   
}  
<join_type> ::=   
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]  
    JOIN  
<pivoted_table> ::=  
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]  
<pivot_clause> ::=  
        ( aggregate_function ( value_column [ [ , ]...n ])   
        FOR pivot_column   
        IN ( <column_list> )   
    )   
<unpivoted_table> ::=  
    table_source UNPIVOT <unpivot_clause> [ [ AS ] table_alias ]  
<unpivot_clause> ::=  
    ( value_column FOR pivot_column IN ( <column_list> ) )   
<column_list> ::=  
    column_name [ ,...n ]   
<system_time> ::=  
{  
       AS OF <date_time>  
    |  FROM <start_date_time> TO <end_date_time>  
    |  BETWEEN <start_date_time> AND <end_date_time>  
    |  CONTAINED IN (<start_date_time> , <end_date_time>)   
    |  ALL  
}  
    <date_time>::=  
        <date_time_literal> | @date_time_variable  
    <start_date_time>::=  
        <date_time_literal> | @date_time_variable  
    <end_date_time>::=  
        <date_time_literal> | @date_time_variable

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
FROM { <table_source> [ ,...n ] }  
<table_source> ::=   
{  
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
    | <joined_table>  
}  
<joined_table> ::=   
{  
    <table_source> <join_type> <table_source> ON search_condition   
    | <table_source> CROSS JOIN <table_source>     | left_table_source { CROSS | OUTER } APPLY right_table_source   
    | [ ( ] <joined_table> [ ) ]   
}  
<join_type> ::=   
    [ INNER ] [ <join hint> ] JOIN  
    | LEFT  [ OUTER ] JOIN  
    | RIGHT [ OUTER ] JOIN  
    | FULL  [ OUTER ] JOIN  
<join_hint> ::=   
    REDUCE  
    | REPLICATE  
    | REDISTRIBUTE

参数

<table_source>

指定用在Transact-SQL语句中的表、视图、表变量，或派生表源，带有别名或不带有别名。一条语句中最多可用多达256个表源，虽然此限制很大程度上取决于可用内存，以及查询中其它表达式的复杂性。单个查询可能不能支持多达256个表源。

FROM关键字后面的表源的顺序不影响返回的结果。当FROM子句中出现重复名称时，SQL Server返回错误。

table_or_view_name

是表或视图的名称。

如果表或视图存在于同一SQL Server实例的其它数据库中，请使用完整的名称，格式为database.schema.object_name。

如果表或视图存在于SQL Server实例外面，请使用四部分名称，格式为：linked_server.catalog.schema.object。欲进一步了解，请参阅sp_addlinkedserver。通过使用OPENDATASOURCE函数构成了四部分名称，其中名称的服务器部分还可以用于指定远程表源。在指定OPENDATASOURCE时，database_name和schema_name可能不适用于所有数据源，并服从于访问远程对象的OLE DB提供者的功能。

[AS] table_alias

是针对table_source的别名，既是为于方便也是为了区分自交连或子查询中的表或视图。别名通常是一个缩写表名，用于在交连中引用表的特定列。如果相同的列名存在于不止一个交连表中，SQL Server需要列名用表名或视图名或别名标明。如果已经定义了别名，表名就不能用了。

如果用了派生表、行集或表值函数，或操作子句（譬如PIVOT 或UNPIVOT），子句的末尾的必要的table_alias是针对所有列（包括分组列）的关联表名，返回了此关联表名。

WITH (<table_hint> )

指定查询优化器针对此语句，对表使用了优化或锁定策略。欲进一步了解，请参阅Table Hints。

rowset_function

Applies to: SQL Server 2008 through SQL Server 2016 and SQL Database.

指定一个行集函数，譬如OPENROWSET，它返回可用来代替表引用的对象。欲进一步了解，请参阅行集函数。

使用OPENROWSET和OPENQUERY函数来指定远程对象，取决于访问对象的OLE DB提供者的性能。

bulk_column_alias

Applies to: SQL Server 2008 through SQL Server 2016 and SQL Database.

是可选的别名，用来代替结果集中的列名。列别名只允许在使用OPENROWSET函数以及BULK选项的SELECT语句中。如果使用了bluk_column_alias，针对每个表列指定别名，以文件中的列相同的顺序。

user_defined_function

指定表值函数。

OPENXML <openxml_clause>

Applies to: SQL Server 2008 through SQL Server 2016 and SQL Database.

在XML文档上提供行集视图。欲进一步了解，请参阅OPENXML。

derived_table

是子查询，检索来自数据库的行。derived_table用作对外部查询的输入。

derived_table可以使用Transact-SQL表值构造器功能来指定多个行。例如，SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);。欲进一步了解，请参阅表值构造器。。

column_alias

是可选的别名，用来代替派生表的结果集中的列名。包括一个列别名，针对Select列表中的每个列，请用圆括号把整个列别名的列表包围起来。

table_or_view_name FOR SYSTEM_TIME <system_time>

Applies to: SQL Server 2016 through SQL Server 2016 and SQL Database.

指定一个数据的专有版本，是返回自指定的临时表以及它的链接的系统版本历史表。

<tablesample_clause>

指定要返回的来自表的数据的范例。此范例可能是近似值。此子句用在SELECT语句、UPDATE语句或DELETE语句的任何主表或交联表中。不能用视图指定TABLESAMPLE。

SYSTEM

是一个独立于编译器的范例方法，由ISO标准指定。在SQL Server中，这是唯一可用的范例方法，默认应用它。SYSTEM应用到基于页的范例方法，其中来自表的一个随机页的集合作为范例被选中，返回那个页中所有的行，作为范例子集。

sample_number

是一个精确或近似的常量数字表达式，代表行数或行的百分比。当用PERCENT指定时，sample_number潜在地转换成浮点数值；否则，它被转换为bigint。PERCENT是默认值。

PERCENT

指定必须从表中检索到表中行的sample_number%。当指定PERCENT时，SQL Server返回指定百分比近似值。当指定了PERCENT时，sample_number表达式必须计算为一个从0到100的值。

ROWS

指定将被检索的近似行数sample_number。指定了ROWS时，SQL Server返回指定行数的近似值。指定ROWS时，sample_number表达式必须计算为一个大于0的整型数。

REPEATABLE

指示选中的范例，可以再次返回的范例。用相同的repeat_seed值指定时，SQL Server将返回行的相同的子集，只要表中的行没有作任何更改。如果用不同的repeat_seed值指定它，SQL Server很有可能返回表不的行的一些不同的范例。下列对表的操作被视为更改：insert、update、delete、索引重建或整理碎片、恢复数据库或附加数据库。

repeat_seed

是一个常数整型表达式，SQL Server用来生成随机数。repeat_seed是bigint。如果没有指定repeat_seed，SQL Server会随机分配一个值。对于特定的repeat_seed值，如果没有对表应用更改，范例结果始终是相同的。repeat_seed表达式必须计算为大于0的整型数。

<joined_table>

是产生两个表或多个表的结果集。对于多表交连，使用括号来更改交联的自然顺序。

<join_type>

指定交联操作的类型。

INNER

指定行的所有匹配对都会返回。从两个表中丢弃不匹配的行。如果不指定交联类型，这是默认值。

FULL [ OUTER ]

指定来自左表或右表的不满足交联条件的行也会包含在结果集中，对应于其它表的输出列被设置为Null。此外还有由INNER JOIN返回的所有的行。

LEFT [ OUTER ]

指定来自左表的不满足交联条件的所有行也会包含在结果集中，对应于其它表的输出列被设置为Null，此外还有内联返回的所有行。

RIGHT [OUTER]

指定来自右表的不满足交联条件的所有行也会包含在结果集中，对应于其它表的输出列被设置为Null，此外还有内联返回的所有行。

<join_hint>

对于SQL Server和SQL Database，指定SQL Server查询优化器对每个指定要查询FROM子句中的交联使用一个交联提示，或执行算法。欲进一步了解，请参阅Join Hints。

对于SQL Data Warehouse和Parallel Data Warehouse，这些交联提示应用于两个分布式非兼容列的INNER交联。他们可以通过限制出现在查询过程中的数据运动的总量，来提升查询性能。针对SQL Data和Parallel Data Warehouse可允许的交联提示是以下这些：

REDUCE

对于交联右边的表，减少要移动的行数，从而使两个分布的不兼容表兼容。REDUCE提示又被称为半交联提示。

REPLICATE

导致交联列的值，来自交联的左侧表的交联列，被重复到所有的节点。右边的表交联起来以重复那些列的版本。

REDISTRIBUTE

强迫两个数据源分布在用JOIN子句指定的列上。对于分布的表，Parallel Data Warehouse将实施一个洗牌移动。对于复制表，Parallel Data Warehouse将实施一个修剪移动。若要理解这些移动类型，请参阅Parallel Data Warehouse产品文档中的“理解查询计划”一文中的“DMS查询计划操作”部分。当查询计划使用广播移动来解决分布式不兼容交联时，此提示增进了性能。

JOIN

标识指定的交联操作应该出现在指定的表源或视图之间。

ON <search_condition>

指定交联所基于的条件。可以用任何谓语指定条件，虽然经常是使用列和比较运算符，例如：

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

当条件指定列时，列不需要具有相同的列名，或者相同的数据类型；然而，如果数据类型不一样的话，它们要么得是兼容的，要么SQL Server可以潜在地转换数据类型。如果数据类型不能潜在地转换，必须用CONVERT函数显式地转换条件的数据类型。

可以有谓语只涉及ON子句中的一个交联表。这样的谓语也可以处于查询的WHERE子句中。虽然这样的谓语的位置并不改变INNER交联，但是当涉及OUTER交联时，它们可能导致不同的结果。这是因为ON子句中的谓语在交联之前先应用于表，而WHERE子句，顾名思义，应用于交联的结果。

欲进一步了解搜索条件和谓语，请参阅搜索条件。

CROSS JOIN

指定两个表的交叉产物。返回同样的行，就像在旧样式（非SQL-91样式的交联）中没有指定WHERE子句。

left_table_source { CROSS | OUTER } APPLY right_table_source

指定APPLY运算的right_table_source 是针对left_table_source的每一行计算的。当right_table_source包含了表值函数，要从left_table_source中取列值，作为它的参数之一时，此功能特别有用。

CROSS或OUTER都必须用APPLY指定。在指定了CROSS时，如果针对left_table_source的指定行计算right_table_source，并返回空结果集，就不会产生任何行。

当指定了OUTER时，对left_table_source的每一行产生了一行，哪怕right_table_source是针对行计算的，并返回一个空结果集。

欲进一步了解，请参阅备注部分。

left_table_source

是定义在前面参数中的表源。欲进一步了解，请参阅备注部分。

right_table_source

是定义在前面参数中的表源。欲进一步了解，请参阅备注部分。

table_source PIVOT <pivot_clause>

指定table_source是以pivot_column为中心轴的。table_source是表或表表达式。输出是一个表，包含了table_source的所有列，除了pivot_column和value_column。table_source中的列，除了pivot_column和value_column，被称为轴运算符的分组列。欲进一步了解PIVOT和UNPIVOT，请参阅使用PIVOT和UNPIVOT。

PIVOT在输入表上根据分组列实施了分组运算，并且为每个组返回一行。此外，输出为column_list中指定的每个值包含一列，它出现在input_table的pivot_column 中。

欲进一步了解，请参阅下面的备注部分。

aggregate_function

是系统合计函数或用户定义的合计函数，接受一个或多个输入。合计函数对Null值应该保持不变。一个涉及到NULL值的合计函数，在计算合计值时，并不考虑组中的NULL值。

不允许系统合计函数COUNT(*)。

value_column

是PIVOT操作的值列。当配合UNPIVOT时，value_column不能是输入的table_source中已有的列的名称。

FOR pivot_column

是PIVOT运算的轴列。pivot_column必须是可以潜在转换为nvarchar()的类型，或者明确地转换为nvarchar()。此列不能是image或rowversion。

用了UNPIVOT时，pivot_column是输出列的名称，来自table_source，它变窄了。不能用table_source表中已有的列作为名称。

IN (column_list )

在PIVOT子句中，pivot_column表中列出的值将变成输出表的列名。此列表不能指定任何已经存在于输入的table_source中作为支轴的列名称。

在UNPIVOT子句中，在table_source中列出的列将被收窄到一个pivot_column。

table_alias

是输出表的别名。必须指定pivot_table_alias。

UNPIVOT < unpivot_clause >

指定输入表把来自column_list中的多列收窄到一个称为pivot_column的列。欲进一步了解PIVOT和UNPIVOT，请参阅使用PIVOT和UNPIVOT。

AS OF <date_time>

适用于： SQL Server 2016到 SQL Server 2016以及SQL　Database

返回一个表，它针对每一行产生一条记录，包含实际上在过去某时的指定点的值。在内部，在瞬时表和它的历史表之间实施了一次并联union，结果被筛选出来，在行里返回值，它在用<date_time>参数指定的时间点是有效的。如果system_start_time_column_name值小于或等于<date_time>参数值，且system_end_time_column_name值大于<date_time>参数值，针对一行的值即被认为是有效的。

FROM <start_date_time>TO <end_date_time>

适用于： SQL Server 2016到 SQL Server 2016以及SQL　Database

返回一个表，其值针对在指定时间范围内活动的所有的记录版本，无论它们是在针对FROM指令的<start_date_time>参数之前开始活动的，还是在针对TO指令的<end_date_time>参数之后停止活动的。在临时表和它的历史表之间内部实施了一个union并集，并且结果被筛选，以返回在指定时间范围内活动的所有的行版本的值。恰好在FROM端点定义的下界变成活动的行也包括在内，但是恰好在由TO端点定义的上界变成活动的行并不包括在内。

BETWEEN <start_date_time>AND <end_date_time>

适用于： SQL Server 2016到 SQL Server 2016以及SQL　Database

和上面关于FROM <start_date_time>TO <end_date_time>的描述差不多，但是它包括恰好在由<end_date_time>端点定义的上界变成活动的行。

CONTAINED IN (<start_date_time> , <end_date_time>)

适用于： SQL Server 2016到 SQL Server 2016以及SQL　Database

返回一个表，其值针对在指定的时间范围内打开和关闭的所有记录版本，CONTAINED IN参数中的两个datetime值定义了该时间范围。恰好在下界变成活动的行，以及恰好在上界停止活动的行也包括在内。

ALL

返回一个表，带有来自当前表和历史表的所有的行的值。

备注

FROM子句对交联表和派生表，支持SQL-92-SQL语法。SQL-92提供了INNER、LEFT OUTER、RIGHT OUTER、FULL OUTER和CROSS交联运算。

在视图和派生表内以及子查询内部，FROM子句内部的UNION和JOIN是受支持的。

自交联是一个表与它自己作交联。基于自交联的插入或更新操作遵循FROM子句的顺序。

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.

使用APPLY

APPLY运算符的左侧运算数和右侧运算数都是表表达式。这些运算数的主要区别是，right_table_source可以使用表值函数，该表值函数取来自left_table_source中的一个列作为函数的参数值之一。left_table_source也可以包含表值函数，但是它不能取用来自right_table_source的列作为参数。

APPLY运算符用以下方式工作，以产生针对FROM子句的表源：

1. 针对left_table_source中的每一行计算right_table_source以产生行集。

   right_table_source中的值取决于left_table_source。可以大致用这种方式表达right_table_source：TVF(left_table_source.row)，其中TVF是表值函数。

2. 通过实施UNION ALL运算求得right_table_source和left_table_source的值，针对每一行产生的结果集作组合操作。
   The list of columns produced by the result of the APPLY operator is the set of columns from the left_table_source that is combined with the list of columns from the right_table_source.

使用PIVOT和UNPIVOT

The pivot_column and value_column are grouping columns that are used by the PIVOT operator. PIVOT follows the following process to obtain the output result set:

1. Performs a GROUP BY on its input_table against the grouping columns and produces one output row for each group.

   输出行中的分组列遵从针对input_table中的组所对应的列值。

2. 根据以下规则生成针对列表表中的列的值，用于每个输出行：
   1. Grouping additionally the rows generated in the GROUP BY in the previous step against the pivot_column.

      对于column_list中的每个输出列，选择满足条件的子组：

      pivot_column = CONVERT(<data type of pivot_column>, 'output_column')

   2. aggregate_function is evaluated against thevalue_column on this subgroup and its result is returned as the value of the corresponding output_column. 如果子组是空的，SQL Server针对output_column生成了一个NULL值。如果合计函数是COUNT，子组是空，则返回零（0）。

欲进一步了解PIVOT和UNPIVOT包括示例，请参阅使用PIVOT和UNPIVOT。

授权

要求DELETE、SELECT或UPDATE语句的权限。

示例

A. 使用简单的FROM子句

下面的示例在示例数据库AdventureWorks2012中，检索来自SalesTerritory表的TerritoryID列和Name列。

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. 使用TABLOCK和HOLDLOCK优化器提示

The following partial transaction shows how to place an explicit shared table lock on Employee and how to read the index. The lock is held throughout the whole transaction.

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

C. 使用SQL-92 CROSS JOIN语法

The following example returns the cross product of the two tables Employee and Department in the AdventureWorks2012 database. A list of all possible combinations of BusinessEntityID rows and all Department name rows are returned.

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. 使用SQL-92 FULL OUTER JOIN语法

The following example returns the product name and any corresponding sales orders in the SalesOrderDetail table in the AdventureWorks2012 database. It also returns any sales orders that have no product listed in the Product table, and any products with a sales order other than the one listed in the Product table.

-- 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  
ORDER BY p.Name ;

E. 使用SQL-92 LEFT OUTER JOIN语法

下面的示例在ProductID中交联了两个表，并保留了来自左表的未匹配的行。Product表与SalesOrderDetail 表作匹配，用来自两个表的ProductID列作为匹配条件。所有的产品，已订单的和未订单的，都出现在结果集中。

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. 使用SQL-92 INNER JOIN语法

下面的示例返回所有的产品名称和销售订单ID。

-- 默认情况下，如果只指定了JOIN关键字，SQL Server执行INNER JOIN
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. 使用SQL-92 RIGHT OUTER JOIN语法

下面的示例在TerritoryID上交联两个列，保留来自右表的不匹配的行。The SalesTerritory table is matched with the SalesPerson table on the TerritoryID column in each table. All salespersons appear in the result set, whether or not they are assigned a territory.

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. 使用HASH和MERGE交联提示

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 Productand ProductVendor MERGE join (p and pv) are HASH joined to the Vendor table to produce (p and pv) and v.

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. 使用派生表

下面的示例使用了派生表，SELECT语句跟在FROM子句后面，以返回所有雇员的名和姓以及他们生活的城市。

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. 使用TABLESAMPLE来读取来自表中的行的范例的数据

The following example uses TABLESAMPLE in the FROM clause to return approximately 10 percent of all the rows in the Customer table.

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

K. 使用APPLY

The following example assumes that the following tables with the following schema exist in the database:

• Departments: DeptID, DivisionID, DeptName, DeptMgrID
• EmpMgr: MgrID, EmpID
• Employees: EmpID, EmpLastName, EmpFirstName, EmpSalary

  There is also a table-valued function, GetReports(MgrID) that returns the list of all employees (EmpID, EmpLastName, EmpSalary) that report directly or indirectly to the specified MgrID.

  The example uses APPLY to return all departments and all employees in that department. If a particular department does not have any employees, there will not be any rows returned for that department.

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

If you want the query to produce rows for those departments without employees, which will produce null values for the EmpID, EmpLastNameand EmpSalary columns, use OUTER APPLY instead.

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

L. 使用CROSS APPLY

The following example retrieves a snapshot of all query plans residing in the plan cache, by querying the sys.dm_exec_cached_plans dynamic management view to retrieve the plan handles of all query plans in the cache. Then the CROSS APPLY operator is specified to pass the plan handles to sys.dm_exec_query_plan. The XML Showplan output for each plan currently in the plan cache is in the query_plan column of the table that is returned.

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

M. 使用FOR SYSTEM_TIME

Applies to: SQL Server 2016 through SQL Server 2016 and SQL Database.

The following example uses the FOR SYSTEM_TIME AS OF date_time_literal_or_variable argument to return table rows that were actual (current) as of January 1, 2014.

SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME AS OF '2014-01-01'  
WHERE ManagerID = 5

The following example uses the FOR SYSTEM_TIME FROM date_time_literal_or_variable TO date_time_literal_or_variable argument to return all rows that were active during the period defined as starting with January 1, 2013 and ending with January 1, 2014, exclusive of the upper boundary.

SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME FROM '2013-01-01' TO '2014-01-01'  
WHERE ManagerID = 5

The following example uses the FOR SYSTEM_TIME BETWEEN date_time_literal_or_variable AND date_time_literal_or_variable argument to return all rows that were active during the period defined as starting with January 1, 2013 and ending with January 1, 2014, inclusive of the upper boundary.

SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME BETWEEN '2013-01-01' AND '2014-01-01'  
WHERE ManagerID = 5

The following example uses the FOR SYSTEM_TIME CONTAINED IN ( date_time_literal_or_variable, date_time_literal_or_variable ) argument to return all rows that were opened and closed during the period defined as starting with January 1, 2013 and ending with January 1, 2014.

SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME CONTAINED IN ( '2013-01-01', '2014-01-01' )  
WHERE ManagerID = 5

下面的示例使用变量而不是使用字面量来为查询提供数据边界值。

DECLARE @AsOfFrom datetime2 = dateadd(month,-12, sysutcdatetime())  
DECLARE @AsOfTo datetime2 = dateadd(month,-6, sysutcdatetime())  
SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME FROM @AsOfFrom TO @AsOfTo  
WHERE ManagerID = 5

Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

N. 使用简单FROM子句

下面的示例从DimSalesTerritory表检索了SalesTerritoryID列和SalesTerritoryRegion列。

-- Uses AdventureWorks  
SELECT SalesTerritoryKey, SalesTerritoryRegion  
FROM DimSalesTerritory  
ORDER BY SalesTerritoryKey;

O. 使用INNER JOIN语法

The following example returns the SalesOrderNumber, ProductKey, and EnglishProductName columns from the FactInternetSales and DimProduct tables where the join key, ProductKey, matches in both tables. The SalesOrderNumber and EnglishProductName columns each exist in one of the tables only, so it is not necessary to specify the table alias with these columns, as is shown; these aliases are included for readability. The word AS before an alias name is not required but is recommended for readability and to conform to the ANSI standard.

-- Uses AdventureWorks  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis INNER JOIN DimProduct AS dp  
ON dp.ProductKey = fis.ProductKey;

Since the INNER keyword is not required for inner joins, this same query could be written as:

-- Uses AdventureWorks  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales fis JOIN DimProduct dp  
ON dp.ProductKey = fis.ProductKey;

还可以配合此查询使用WHERE子句，以限制结果。此示例限制结果为SalesOrderNumber值高于‘SO5000’：

-- Uses AdventureWorks  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis JOIN DimProduct AS dp  
ON dp.ProductKey = fis.ProductKey  
WHERE fis.SalesOrderNumber > 'SO50000'  
ORDER BY fis.SalesOrderNumber;

P. 使用LEFT OUTER JOIN和RIGHT OUTER JOIN语法

The following example joins the FactInternetSales and DimProducttables on the ProductKey columns. The left outer join syntax preserves the unmatched rows from the left (FactInternetSales) table. Since the FactInternetSales table does not contain any ProductKey values that do not match the DimProduct table, this query returns the same rows as the first inner join example above.

-- Uses AdventureWorks  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis LEFT OUTER JOIN DimProduct AS dp  
ON dp.ProductKey = fis.ProductKey;

此查询还可以被写为不带有OUTER关键字。

在左外部交联中，来自右表的不匹配的行被保留下来。下面的示例返回与上面的左外部交联相同的行。

-- Uses AdventureWorks  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM DimProduct AS dp RIGHT OUTER JOIN FactInternetSales AS fis  
ON dp.ProductKey = fis.ProductKey;

The following query uses the DimSalesTerritory table as the left table in a left outer join. It retrieves the SalesOrderNumber values from the FactInternetSales table. If there are no orders for a particular SalesTerritoryKey, the query will return a NULL for the SalesOrderNumber for that row. This query is ordered by the SalesOrderNumber column, so that any NULLs in this column will appear at the top of the results.

-- Uses AdventureWorks  
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst LEFT OUTER JOIN FactInternetSales AS fis  
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey  
ORDER BY fis.SalesOrderNumber;

此查询可以被重写为利用右外交联，以检查到相同的结果：

-- Uses AdventureWorks  
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber  
FROM FactInternetSales AS fis RIGHT OUTER JOIN DimSalesTerritory AS dst  
ON fis.SalesTerritoryKey = dst.SalesTerritoryKey  
ORDER BY fis.SalesOrderNumber;

Q. 使用FULL OUTER JOIN语法

下面的示例演示了全外交联，它返回两个交联表的所有行，对于不匹配另一个表的列值返回Null。

-- Uses AdventureWorks  
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst FULL OUTER JOIN FactInternetSales AS fis  
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey  
ORDER BY fis.SalesOrderNumber;

此查询还可以被重写为不带OUTER关键字。

-- Uses AdventureWorks  
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst FULL JOIN FactInternetSales AS fis  
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey  
ORDER BY fis.SalesOrderNumber;

R. 使用CROSS JOIN语法

The following example returns the cross-product of the FactInternetSales and DimSalesTerritory tables. A list of all possible combinations of SalesOrderNumber and SalesTerritoryKey are returned. Notice the absence of the ON clause in the cross join query.

-- Uses AdventureWorks  
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst CROSS JOIN FactInternetSales AS fis  
ORDER BY fis.SalesOrderNumber;

S. 使用派生表

The following example uses a derived table (a SELECT statement after the FROM clause) to return the CustomerKey and LastName columns of all customers in the DimCustomer table with BirthDate values later than January 1, 1970 and the last name ‘Smith’.

-- Uses AdventureWorks  
SELECT CustomerKey, LastName  
FROM  
   (SELECT * FROM DimCustomer  
    WHERE BirthDate > '01/01/1970') AS DimCustomerDerivedTable  
WHERE LastName = 'Smith'  
ORDER BY LastName;

T. 减少交联提示示例

The following example uses the REDUCE join hint to alter the processing of the derived table within the query. When using the REDUCE join hint in this query, the fis.ProductKey is projected, replicated and made distinct, and then joined to DimProduct during the shuffle of DimProduct on ProductKey. The resulting derived table is distributed on fis.ProductKey.

-- Uses AdventureWorks  
EXPLAIN SELECT SalesOrderNumber  
FROM  
   (SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
    FROM DimProduct AS dp   
      INNER REDUCE JOIN FactInternetSales AS fis   
      ON dp.ProductKey = fis.ProductKey  
   ) AS dTable  
ORDER BY SalesOrderNumber;

U. 重复交联提示示例

This next example shows the same query as the previous example, except that a REPLICATE join hint is used instead of the REDUCE join hint. Use of the REPLICATE hint causes the values in the ProductKey (joining) column from the FactInternetSales table to be replicated to all nodes. The DimProduct table is joined to the replicated version of those values.

-- Uses AdventureWorks  
EXPLAIN SELECT SalesOrderNumber  
FROM  
   (SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
    FROM DimProduct AS dp   
      INNER REPLICATE JOIN FactInternetSales AS fis  
      ON dp.ProductKey = fis.ProductKey  
   ) AS dTable  
ORDER BY SalesOrderNumber;

V. 使用REDISTRIBUTE提示来生成洗牌移动，针对分布式不兼容交联

下面的查询在分布式不兼容交联中使用了REDISTRIBUTE提示。这保证了查询优化器将在查询计划中使用洗牌移动。This also guarantees the query plan will not use a Broadcast move which moves a distributed table to a replicated table.

In the following example, the REDISTRIBUTE hint forces a Shuffle move on the FactInternetSales table because ProductKey is the distribution column for DimProduct, and is not the distribution column for FactInternetSales.

-- Uses AdventureWorks  
EXPLAIN  
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost  
FROM DimProduct dp INNER REDISTRIBUTE JOIN FactInternetSales fis  
ON dp.ProductKey = fis.ProductKey;