FROM (Transact-SQL)FROM (Transact-SQL)

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

指定在 SQL Server 2017SQL Server 2017 的 DELETE、SELECT 和 UPDATE 语句中使用的表、视图、派生表和联接表。Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in SQL Server 2017SQL Server 2017. 在 SELECT 语句中,FROM 子句是必需的,除非选择列表只包含常量、变量和算术表达式(没有列名)。In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- 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 
    [<tablesample_clause>]  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
    | <joined_table>  
}  
  
<tablesample_clause> ::=
    TABLESAMPLE ( sample_number [ PERCENT ] ) -- SQL Data Warehouse only  
 
<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  

参数Arguments

<table_source><table_source>
指定要在 Transact-SQLTransact-SQL 语句中使用的表、视图、表变量或派生表源(有无别名均可)。Specifies a table, view, table variable, or derived table source, with or without an alias, to use in the Transact-SQLTransact-SQL statement. 虽然语句中可用的表源个数的限值根据可用内存和查询中其他表达式的复杂性而有所不同,但一个语句中最多可使用 256 个表源。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. 单个查询可能不支持最多有 256 个表源。Individual queries may not support up to 256 table sources.

备注

如果查询中引用了许多表,查询性能会受到影响。Query performance may suffer with lots of tables referenced in a query. 编译和优化时间也受到其他因素的影响。Compilation and optimization time is also affected by additional factors. 这些因素包括:每个 <table_source> 是否有索引和索引视图,以及 SELECT 语句中 <select_list> 的大小。These include the presence of indexes and indexed views on each <table_source> and the size of the <select_list> in the SELECT statement.

表源在 FROM 关键字后的顺序不影响返回的结果集。The order of table sources after the FROM keyword does not affect the result set that is returned. 如果 FROM 子句中出现重复的名称,SQL ServerSQL Server 会返回错误。SQL ServerSQL Server returns errors when duplicate names appear in the FROM clause.

table_or_view_nametable_or_view_name
表或视图的名称。Is the name of a table or view.

如果表或视图存在于 SQL ServerSQL Server 的同一实例的另一个数据库中,请按照 database.schema.object_name 形式使用完全限定名称。If the table or view exists in another database on the same instance of SQL ServerSQL Server, use a fully qualified name in the form database.schema.object_name.

如果表或视图不在 SQL ServerSQL Server 的实例中,请按照 linked_server.catalog.schema.object 的形式使用由四个部分组成的名称。If the table or view exists outside the instance of SQL ServerSQL Serverl, use a four-part name in the form linked_server.catalog.schema.object. 有关详细信息,请参阅 sp_addlinkedserver (Transact-SQL)的数据。For more information, see sp_addlinkedserver (Transact-SQL). 如果由四个部分组成的名称的服务器部分使用的是 OPENDATASOURCE 函数,则该名称也可用于指定远程表源。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. 如果指定 OPENDATASOURCE,则 database_name 和 schema_name 可能不适用于所有数据源,并且受到访问远程对象的 OLE DB 提供程序的性能的限制。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[AS] table_alias
table_source 的别名,别名可带来使用上的方便,也可用于区分自联接或子查询中的表或视图。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. 如果联接中的多个表中存在相同的列名,SQL ServerSQL Server 要求使用表名、视图名或别名来限定列名。If the same column name exists in more than one table in the join, SQL ServerSQL 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.

如果使用派生表、行集或表值函数或者运算符子句(如 PIVOT 或 UNPIVOT),则在子句结尾处必需的 table_alias 是所有返回列(包括分组列)的关联表名。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> )WITH (<table_hint> )
指定查询优化器对此表和此语句使用优化或锁定策略。Specifies that the query optimizer use an optimization or locking strategy with this table and for this statement. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

rowset_functionrowset_function

适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

指定其中一个行集函数(如 OPENROWSET),该函数返回可用于替代表引用的对象。Specifies one of the rowset functions, such as OPENROWSET, that returns an object that can be used instead of a table reference. 有关行集函数的列表的详细信息,请参阅行集函数 (Transact-SQL)For more information about a list of rowset functions, see Rowset Functions (Transact-SQL).

使用 OPENROWSET 和 OPENQUERY 函数指定远程对象依赖于访问该对象的 OLE DB 访问接口的性能。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_aliasbulk_column_alias

适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

代替结果集内列名的可选别名。Is an optional alias to replace a column name in the result set. 只允许在使用 OPENROWSET 函数和 BULK 选项的 SELECT 语句中使用列别名。Column aliases are allowed only in SELECT statements that use the OPENROWSET function with the BULK option. 使用 bulk_column_alias 时,为每个表列指定别名,顺序与这些列在文件中的顺序相同。When you use bulk_column_alias, specify an alias for every table column in the same order as the columns in the file.

备注

此别名覆盖 XML 格式化文件的 COLUMN 元素中的 NAME 属性(如果有该属性)。This alias overrides the NAME attribute in the COLUMN elements of an XML format file, if present.

user_defined_functionuser_defined_function
指定表值函数。Specifies a table-valued function.

OPENXML <openxml_clause>OPENXML <openxml_clause>

适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

通过 XML 文档提供行集视图。Provides a rowset view over an XML document. 有关详细信息,请参阅 OPENXML (Transact-SQL)For more information, see OPENXML (Transact-SQL).

derived_tablederived_table
从数据库中检索行的子查询。Is a subquery that retrieves rows from the database. derived_table 用作外部查询的输入。derived_table is used as input to the outer query.

derived _table 可以使用 Transact-SQLTransact-SQL 表值构造函数功能来指定多个行。derived _table can use the Transact-SQLTransact-SQL table value constructor feature to specify multiple rows. 例如, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);For example, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. 有关详细信息,请参阅表值构造函数 (Transact-SQL)For more information, see Table Value Constructor (Transact-SQL).

column_aliascolumn_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.

table_or_view_name FOR SYSTEM_TIME <system_time>table_or_view_name FOR SYSTEM_TIME <system_time>

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

指定从指定时态表及其链接的系统版本控制的历史记录表返回特定版本的数据Specifies that a specific version of data is returned from the specified temporal table and its linked system-versioned history table

Tablesample 子句Tablesample clause

适用范围: SQL Server、SQL 数据库Applies to: SQL Server, SQL Database

指定返回来自表的数据样本。Specifies that a sample of data from the table is returned. 该样本可以是近似的。The sample may be approximate. 此子句可对 SELECT 或 UPDATE 语句中的任何主表或联接表使用。This clause can be used on any primary or joined table in a SELECT or UPDATE statement. 不能对视图指定 TABLESAMPLE。TABLESAMPLE cannot be specified with views.

备注

对升级到 SQL ServerSQL Server 的数据库使用 TABLESAMPLE 时,数据库的兼容级别必须设置为 110 或更高,在递归公用表表达式 (CTE) 查询中不允许 PIVOT。When you use TABLESAMPLE against databases that are upgraded to SQL ServerSQL 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. 有关详细信息,请参阅 ALTER DATABASE 兼容级别 (Transact-SQL)For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

SYSTEMSYSTEM
ISO 标准指定的依赖于实现的抽样方法。Is an implementation-dependent sampling method specified by ISO standards. SQL ServerSQL Server 中,这是唯一可用的抽样方法,并且是默认应用的方法。In SQL ServerSQL Server, this is the only sampling method available and is applied by default. SYSTEM 应用基于页的抽样方法,即从表中选择一组随机页作为样本,这些页上的所有行作为样本子集返回。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_numbersample_number
表示行的百分比或行数的精确或近似的常量数值表达式。Is an exact or approximate constant numeric expression that represents the percent or number of rows. 使用 PERCENT 指定时,sample_number 被隐式转换为 float 值;否则,它被转换为 bigint。When specified with PERCENT, sample_number is implicitly converted to a float value; otherwise, it is converted to bigint. PERCENT 是默认设置。PERCENT is the default.

PERCENTPERCENT
指定应该从表中检索表行的 sample_number 百分比。Specifies that a sample_number percent of the rows of the table should be retrieved from the table. 指定 PERCENT 时,SQL ServerSQL Server 返回指定的百分比的近似值。When PERCENT is specified, SQL ServerSQL Server returns an approximate of the percent specified. 指定 PERCENT 时,sample_number 表达式的结果必须是 0 到 100 之间的值。When PERCENT is specified the sample_number expression must evaluate to a value from 0 to 100.

ROWSROWS
指定将检索的行的近似 sample_number。Specifies that approximately sample_number of rows will be retrieved. 指定 ROWS 时,SQL ServerSQL Server 返回指定的行数的近似值。When ROWS is specified, SQL ServerSQL Server returns an approximation of the number of rows specified. 指定 ROWS 时,sample_number 表达式的结果必须是大于零的整数值。When ROWS is specified, the sample_number expression must evaluate to an integer value greater than zero.

REPEATABLEREPEATABLE
指示可以再次返回选定的样本。Indicates that the selected sample can be returned again. 使用同一个 repeat_seed 值指定时,只要对表中任何行尚未进行更改,SQL ServerSQL Server 就会返回相同的行集。When specified with the same repeat_seed value, SQL ServerSQL Server will return the same subset of rows as long as no changes have been made to any rows in the table. 使用其他 repeat_seed 值指定时,SQL ServerSQL Server 很可能将返回表中行的某些不同样本。When specified with a different repeat_seed value, SQL ServerSQL 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_seedrepeat_seed
SQL ServerSQL Server 用于生成随机数的常量整数表达式。Is a constant integer expression used by SQL ServerSQL Server to generate a random number. repeat_seed 是 bigint。repeat_seed is bigint. 如果未指定 repeat_seed,SQL ServerSQL Server 将随机分配值。If repeat_seed is not specified, SQL ServerSQL Server assigns a value at random. 对于特定的 repeat_seed 值,如果尚未对表应用任何更改,抽样结果始终相同。For a specific repeat_seed value, the sampling result is always the same if no changes have been applied to the table. repeat_seed 表达式的值必须是大于零的整数。The repeat_seed expression must evaluate to an integer greater than zero.

Tablesample 子句Tablesample clause

适用范围: SQL 数据仓库Applies to: SQL Data Warehouse

指定返回来自表的数据样本。Specifies that a sample of data from the table is returned. 该样本可以是近似的。The sample may be approximate. 此子句可对 SELECT 或 UPDATE 语句中的任何主表或联接表使用。This clause can be used on any primary or joined table in a SELECT or UPDATE statement. 不能对视图指定 TABLESAMPLE。TABLESAMPLE cannot be specified with views.

PERCENTPERCENT
指定应该从表中检索表行的 sample_number 百分比。Specifies that a sample_number percent of the rows of the table should be retrieved from the table. 指定 PERCENT 时,SQL 数据仓库返回指定的百分比的近似值。When PERCENT is specified, SQL Data Warehouse returns an approximate of the percent specified. 指定 PERCENT 时,sample_number 表达式的结果必须是 0 到 100 之间的值。When PERCENT is specified, the sample_number expression must evaluate to a value from 0 to 100.

联接的表Joined table

联接的表是由两个或更多表的积构成的结果集。A 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.

INNERINNER
指定返回所有匹配的行对。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 ]FULL [ OUTER ]
指定在结果集中包括左表或右表中不满足联接条件的行,并将对应于另一个表的输出列设为 NULL。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. 这是对通常由 INNER JOIN 返回的所有行的补充。This is in addition to all rows typically returned by the INNER JOIN.

LEFT [ OUTER ]LEFT [ OUTER ]
指定在结果集中包括左表中所有不满足联接条件的行,除了由内部联接返回所有的行之外,还将另外一个表的输出列设置为 NULL。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]RIGHT [OUTER]
指定在结果集中包括右表中所有不满足联接条件的行,除了由内部联接返回所有的行之外,还将与另外一个表对应的输出列设置为 NULL。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

对于 SQL ServerSQL ServerSQL 数据库SQL Database,指定 SQL ServerSQL Server 查询优化器为在查询的 FROM 子句中指定的每个联接使用一个联接提示或执行算法。For SQL ServerSQL Server and SQL 数据库SQL Database, specifies that the SQL ServerSQL Server query optimizer use one join hint, or execution algorithm, per join specified in the query FROM clause. 有关详细信息,请参阅联接提示 (Transact-SQL)For more information, see Join Hints (Transact-SQL).

对于 SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse,这些联接提示应用于两个分布不兼容列上的 INNER 联接。For SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse, these join hints apply to INNER joins on two distribution incompatible columns. 它们可以通过限制查询处理期间发生的数据移动量来提高查询性能。They can improve query performance by restricting the amount of data movement that occurs during query processing. 允许用于 SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehouse的联接提示如下:The allowable join hints for SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse are as follows:

REDUCEREDUCE
减少联接右侧表中要移动的行的数量,以使两个分布不兼容表实现兼容。Reduces the number of rows to be moved for the table on the right side of the join in order to make two distribution incompatible tables compatible. REDUCE 提示也称为半联接提示。The REDUCE hint is also called a semi-join hint.

REPLICATEREPLICATE
导致联接左侧表中的联接列中的值复制到所有节点。Causes the values in the joining column from the table on the left side of the join to be replicated to all nodes. 右侧表将与这些列的复制版本进行联接。The table on the right is joined to the replicated version of those columns.

REDISTRIBUTEREDISTRIBUTE
强制两个数据源分布到 JOIN 子句中指定的列上。Forces two data sources to be distributed on columns specified in the JOIN clause. 对于分布式表,并行数据仓库Parallel Data Warehouse 将执行 shuffle 移动。For a distributed table, 并行数据仓库Parallel Data Warehouse will perform a shuffle move. 对于复制的表,并行数据仓库Parallel Data Warehouse将执行 trim 移动。For a replicated table, 并行数据仓库Parallel Data Warehouse will perform a trim move. 要了解这些移动类型,请参阅 并行数据仓库产品文档Parallel Data Warehouse product documentation 中“了解查询计划”主题中的“DMS 查询计划操作”部分。To understand these move types, see the "DMS Query Plan Operations" section in the "Understanding Query Plans" topic in the 并行数据仓库产品文档Parallel Data Warehouse product documentation. 当查询计划使用 broadcast 移动解决分布不兼容联接时,此提示可提高性能。This hint can improve performance when the query plan is using a broadcast move to resolve a distribution incompatible join.

JOINJOIN
指示指定的联接操作应在指定的表源或视图之间执行。Indicates that the specified join operation should occur between the specified table sources or views.

ON <search_condition>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:

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

当该条件指定列时,列不必具有相同的名称或数据类型;但是,如果数据类型不相同,则这些列要么必须相互兼容,要么是 SQL ServerSQL Server 能够隐式转换的类型。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 ServerSQL Server can implicitly convert. 如果数据类型不能隐式转换,则在条件中必须使用 CONVERT 函数显式转换数据类型。If the data types cannot be implicitly converted, the condition must explicitly convert the data type by using the CONVERT function.

在 ON 子句中可能有仅涉及一个联接表的谓词。There can be predicates that involve only one of the joined tables in the ON clause. 这样的谓词也可能出现在查询中的 WHERE 子句中。Such predicates also can be in the WHERE clause in the query. 虽然这种谓词的放置对于 INNER 联接不会产生差别,但是在涉及 OUTER 联接时可能会导致不同的结果。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. 这是因为 ON 子句中的谓词在应用于联接之前先应用于表,而 WHERE 子句在语义上应用于联接结果。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.

有关搜索条件和谓词的详细信息,请参阅搜索条件 (Transact-SQL)For more information about search conditions and predicates, see Search Condition (Transact-SQL).

CROSS JOINCROSS JOIN
指定两个表的叉积。Specifies the cross-product of two tables. 返回相同的行,就好像在旧式的非 SQL-92 式联接中并没有指定 WHERE 子句。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_sourceleft_table_source { CROSS | OUTER } APPLY right_table_source
指定针对 left_table_source 的每行,对 APPLY 运算符的 right_table_source 求值。Specifies that the right_table_source of the APPLY operator is evaluated against every row of the left_table_source. 当 right_table_source 包含从 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.

必须使用 APPLY 指定 CROSS 或 OUTER。Either CROSS or OUTER must be specified with APPLY. 如果指定 CROSS,针对 left_table_source 的指定行对 right_table_source 求值,且返回了空的结果集,则不生成任何行。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.

如果指定 OUTER,则为 left_table_source 的每行生成一行,即使在针对该行对 right_table_source 求值且返回空的结果集时也是如此。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_sourceleft_table_source
上一个参数中定义的一个表源。Is a table source as defined in the previous argument. 有关详细信息,请参见“备注”部分。For more information, see the Remarks section.

right_table_sourceright_table_source
上一个参数中定义的一个表源。Is a table source as defined in the previous argument. 有关详细信息,请参见“备注”部分。For more information, see the Remarks section.

PIVOT 子句PIVOT clause

table_source PIVOT <pivot_clause>table_source PIVOT <pivot_clause>
指定基于 pivot_column 透视 table_source。Specifies that the table_source is pivoted based on the pivot_column. table_source 是一个表或表表达式。table_source is a table or table expression. 输出是包含 table_source 的所有列(pivot_column 和 value_column 除外)的表。The output is a table that contains all columns of the table_source except the pivot_column and value_column. table_source 中的列(pivot_column 和 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 和 UNPIVOT 的详细信息,请参阅使用 PIVOT 和 UNPIVOTFor more information about PIVOT and UNPIVOT, see Using PIVOT and UNPIVOT.

PIVOT 对输入表执行分组列的分组操作,并为每个组返回一行。PIVOT performs a grouping operation on the input table with regard to the grouping columns and returns one row for each group. 此外,input_table 的 pivot_column 中显示的 column_list 中指定的每个值,输出中都对应一列。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_functionaggregate_function
接受一个或多个输入的系统聚合函数或用户定义的聚合函数。Is a system or user-defined aggregate function that accepts one or more inputs. 聚合函数应该对 Null 值固定不变。The aggregate function should be invariant to null values. 对 Null 值固定不变的聚合函数在求聚合值时不考虑组中的 Null 值。An aggregate function invariant to null values does not consider null values in the group while it is evaluating the aggregate value.

不允许使用 COUNT(*) 系统聚合函数。The COUNT(*) system aggregate function is not allowed.

value_columnvalue_column
PIVOT 运算符的值列。Is the value column of the PIVOT operator. 与 UNPIVOT 一起使用时,value_column 不能是输入 table_source 中的现有列的名称。When used with UNPIVOT, value_column cannot be the name of an existing column in the input table_source.

FOR pivot_columnFOR pivot_column
PIVOT 运算符的透视列。Is the pivot column of the PIVOT operator. pivot_column 的数据类型必须可隐式或显式转换为 nvarchar()。pivot_column must be of a type implicitly or explicitly convertible to nvarchar(). 此列不能为 image 或 rowversion。This column cannot be image or rowversion.

使用 UNPIVOT 时,pivot_column 是从 table_source 中提取的输出列的名称。When UNPIVOT is used, pivot_column is the name of the output column that becomes narrowed from the table_source. table_source 中不能有具有该名称的现有列。There cannot be an existing column in table_source with that name.

IN (column_list )IN (column_list )
在 PIVOT 子句中,列出 pivot_column 中将成为输出表的列名的值。In the PIVOT clause, lists the values in the pivot_column that will become the column names of the output table. 该列表不能指定被透视的输入 table_source 中已存在的任何列名。The list cannot specify any column names that already exist in the input table_source that is being pivoted.

在 UNPIVOT 子句中,列出 table_source 中将被提取到单个 pivot_column 中的列。In the UNPIVOT clause, lists the columns in table_source that will be narrowed into a single pivot_column.

table_aliastable_alias
输出表的别名。Is the alias name of the output table. 必须指定 pivot_table_alias。pivot_table_alias must be specified.

UNPIVOT <unpivot_clause>UNPIVOT <unpivot_clause>
指定输入表从 column_list 中的多个列缩减为名为 pivot_column 的单个列。Specifies that the input table is narrowed from multiple columns in column_list into a single column called pivot_column. 有关 PIVOT 和 UNPIVOT 的详细信息,请参阅使用 PIVOT 和 UNPIVOTFor more information about PIVOT and UNPIVOT, see Using PIVOT and UNPIVOT.

AS OF <date_time>AS OF <date_time>

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

返回一个表,其中为包含过去指定时间点的实际(当前)值的每个行提供一条记录。Returns a table with single record for each row containing the values that were actual (current) at the specified point in time in the past. 在内部,时态表及其历史记录表之间将进行联合,然后筛选结果以返回在 <date_time> 参数指定的时间点有效的行中的值。Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. 如果 system_start_time_column_name 值小于或等于 <date_time> 参数值,并且 system_end_time_column_name 值大于 <date_time> 参数值,则此行的值视为有效。The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the <date_time> parameter value and the system_end_time_column_name value is greater than the <date_time> parameter value.

FROM <start_date_time> TO <end_date_time>FROM <start_date_time> TO <end_date_time>

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

返回一个表,其中包含在指定的时间范围内保持活动状态的所有记录版本的值,不管这些版本是在 FROM 自变量的 <start_date_time> 参数之前开始活动,还是在 TO 自变量的 <end_date_time> 参数值之后停止活动。Returns a table with the values for all record versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. 在内部,将在临时表及其历史记录表之间进行联合,然后筛选结果,以返回在指定时间范围内任意时间保持活动状态的所有行版本的值。Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. 正好在 FROM 终结点定义的下限时间激活的行将包括在内,正好在 TO 终结点定义的上限时间激活的行将被排除。Rows that became active exactly on the lower boundary defined by the FROM endpoint are included and rows that became active exactly on the upper boundary defined by the TO endpoint are not included.

BETWEEN <start_date_time> AND <end_date_time>BETWEEN <start_date_time> AND <end_date_time>

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

与上面的 FROM <start_date_time> TO <end_date_time> 描述相同,不过,它包括 <end_date_time> 终结点定义的上限时间激活的行。Same as above in the FROM <start_date_time> TO <end_date_time> description, except it includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.

CONTAINED IN (<start_date_time> , <end_date_time>)CONTAINED IN (<start_date_time> , <end_date_time>)

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

返回一个表,其中包含在 CONTAINED IN 参数的两个日期时间值定义的时间范围内打开和关闭的所有记录版本的值。Returns a table with the values for all record versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. 正好在下限时间激活的记录,或者在上限时间停止活动的行将包括在内。Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.

ALLALL
返回具有当前表和历史记录表中所有行中的值的表。Returns a table with the values from all rows from both the current table and the history table.

RemarksRemarks

FROM 子句支持用于联接表和派生表的 SQL-92-SQL 语法。The FROM clause supports the SQL-92-SQL syntax for joined tables and derived tables. SQL-92 语法提供 INNER、LEFT OUTER、RIGHT OUTER、FULL OUTER 和 CROSS 联接运算符。SQL-92 syntax provides the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS join operators.

视图、派生表和子查询中均支持 FROM 子句内的 UNION 和 JOIN。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. 基于自联接的插入和更新操作遵循 FROM 子句中的顺序。Insert or update operations that are based on a self-join follow the order in the FROM clause.

因为 SQL ServerSQL Server 会考虑来自提供列分布统计信息的链接服务器的分布及基数统计信息,所以,无需 REMOTE 联接提示来强制远程评估联接。Because SQL ServerSQL 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. SQL ServerSQL Server 查询处理器将考虑远程统计信息,并确定远程联接策略是否适当。The SQL ServerSQL Server query processor considers remote statistics and determines whether a remote-join strategy is appropriate. REMOTE 联接提示对不提供列分发统计信息的提供程序非常有用。REMOTE join hint is useful for providers that do not provide column distribution statistics.

使用 APPLYUsing APPLY

APPLY 运算符的左操作数和右操作数都是表表达式。Both the left and right operands of the APPLY operator are table expressions. 这些操作数之间的主要区别是,right_table_source 可以使用表值函数,该函数可从 left_table_source 获取一个列作为函数的参数之一。The main difference between these operands is that the right_table_source can use a table-valued function that takes a column from the left_table_source as one of the arguments of the function. left_table_source 可以包括表值函数,但不能以来自 right_table_source 的列作为参数。The left_table_source can include table-valued functions, but it cannot contain arguments that are columns from the right_table_source.

APPLY 运算符通过以下方式工作,以便为 FROM 子句生成表源:The APPLY operator works in the following way to produce the table source for the FROM clause:

  1. 针对 left_table_source 的每一行计算 right_table_source 以生成行集。Evaluates right_table_source against each row of the left_table_source to produce rowsets.

    right_table_source 中的值取决于 left_table_source。The values in the right_table_source depend on left_table_source. right_table_source 可以按以下方式近似表示:TVF(left_table_source.row),其中,TVF 是表值函数。right_table_source can be represented approximately this way: TVF(left_table_source.row), where TVF is a table-valued function.

  2. 通过执行 UNION ALL 操作,将计算 right_table_source 的值时为每行生成的结果集与 left_table_source 组合起来。Combines the result sets that are produced for each row in the evaluation of right_table_source with the left_table_source by performing a UNION ALL operation.

    APPLY 运算符的结果生成的列的列表是来自 left_table_source(与来自 right_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 和 UNPIVOTUsing PIVOT and UNPIVOT

pivot_column 和 value_column 是 PIVOT 运算符使用的分组列。The pivot_column and value_column are grouping columns that are used by the PIVOT operator. PIVOT 遵循以下过程获得输出结果集:PIVOT follows the following process to obtain the output result set:

  1. 对分组列的 input_table 执行 GROUP BY,为每个组生成一个输出行。Performs a GROUP BY on its input_table against the grouping columns and produces one output row for each group.

    输出行中的分组列获得 input_table 中该组的对应列值。The grouping columns in the output row obtain the corresponding column values for that group in the input_table.

  2. 通过执行以下操作,为每个输出行生成列列表中的列的值:Generates values for the columns in the column list for each output row by performing the following:

    1. 针对 pivot_column,对上一步在 GROUP BY 中生成的行另外进行分组。Grouping additionally the rows generated in the GROUP BY in the previous step against the pivot_column.

      对于 column_list 中的每个输出列,选择满足以下条件的子组:For each output column in the column_list, selecting a subgroup that satisfies the condition:

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

    2. 针对此子组上的 value_column 对 aggregate_function 求值,其结果作为相应的 output_column 的值返回。aggregate_function is evaluated against the value_column on this subgroup and its result is returned as the value of the corresponding output_column. 如果该子组为空,SQL ServerSQL Server 将为该 output_column 生成 NULL 值。If the subgroup is empty, SQL ServerSQL Server generates a null value for that output_column. 如果聚合函数是 COUNT,且子组为空,则返回零 (0)。If the aggregate function is COUNT and the subgroup is empty, zero (0) is returned.

备注

UNPIVOT 子句中的列标识符需遵循目录排序规则。The column identifiers in the UNPIVOT clause follow the catalog collation. 对于 SQL 数据库SQL Database,排序规则始终是 SQL_Latin1_General_CP1_CI_ASFor SQL 数据库SQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. 对于 SQL ServerSQL Server 部分包含的数据库,排序规则始终是 Latin1_General_100_CI_AS_KS_WS_SCFor SQL ServerSQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC. 如果将该列与与其他列合并,则需要 collate 子句 (COLLATE DATABASE_DEFAULT) 以避免冲突。If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

有关 PIVOT 和 UNPIVOT 及示例的详细信息,请参阅使用 PIVOT 和 UNPIVOTFor more information about PIVOT and UNPIVOT including examples, see Using PIVOT and UNPIVOT.

PermissionsPermissions

需要 DELETE、SELECT 或 UPDATE 语句的权限。Requires the permissions for the DELETE, SELECT, or UPDATE statement.

示例Examples

A.A. 使用简单 FROM 子句Using a simple FROM clause

下面的示例从 AdventureWorks2012AdventureWorks2012 示例数据库中的 TerritoryID 表中检索 NameSalesTerritory 列。The following example retrieves the TerritoryID and Name columns from the SalesTerritory table in the AdventureWorks2012AdventureWorks2012 sample database.

SELECT TerritoryID, Name  
FROM Sales.SalesTerritory  
ORDER BY TerritoryID ;  

下面是结果集:Here is the result set.

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.B. 使用 TABLOCK 和 HOLDLOCK 优化器提示Using the TABLOCK and HOLDLOCK optimizer hints

下面的部分事务说明了如何在 Employee 上放置一个显式共享表锁,以及如何读取索引。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.C. 使用 SQL-92 CROSS JOIN 语法Using the SQL-92 CROSS JOIN syntax

下面的示例返回 AdventureWorks2012AdventureWorks2012 数据库中 EmployeeDepartment 这两个表的叉积。The following example returns the cross product of the two tables Employee and Department in the AdventureWorks2012AdventureWorks2012 database. 包含所返回的 BusinessEntityID 行和所有 Department名称行的所有可能组合的列表。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.D. 使用 SQL-92 FULL OUTER JOIN 语法Using the SQL-92 FULL OUTER JOIN syntax

下面的示例返回 AdventureWorks2012AdventureWorks2012 数据库中产品名称以及 SalesOrderDetail 表中任何对应的销售订单。The following example returns the product name and any corresponding sales orders in the SalesOrderDetail table in the AdventureWorks2012AdventureWorks2012 database. 该示例还将返回在 Product 表中没有列出产品的任何销售订单,以及销售订单不同于在 Product 表中列出的销售订单的任何产品。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.E. 使用 SQL-92 LEFT OUTER JOIN 语法Using the SQL-92 LEFT OUTER JOIN syntax

下面的示例基于 ProductID 联接两个表,并保留左表中不匹配的行。The following example joins two tables on ProductID and preserves the unmatched rows from the left table. Product 表与每个表中的 SalesOrderDetail 列上的 ProductID 表相匹配。The Product table is matched with the SalesOrderDetail table on the ProductID columns in each table. 所有产品,无论是否已订购,都将在结果集中显示。All products, ordered and not ordered, appear in the result set.

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

下面的示例返回所有产品名称和销售订单 ID。The following example returns all product names and sales order IDs.

-- 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.G. 使用 SQL-92 RIGHT OUTER JOIN 语法Using the SQL-92 RIGHT OUTER JOIN syntax

下面的示例基于 TerritoryID 联接两个表,并保留右表中不匹配的行。The following example joins two tables on TerritoryID and preserves the unmatched rows from the right table. SalesTerritory 表与每个表中的 TerritoryID 列上的 SalesPerson 表相匹配。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.H. 使用 HASH 和 MERGE 联接提示Using HASH and MERGE join hints

下面的示例在 ProductProductVendorVendor 表之间执行三表联接,生成产品及其供应商的列表。The following example performs a three-table join among the Product, ProductVendor, and Vendor tables to produce a list of products and their vendors. 查询优化器使用 MERGE 联接来联接 ProductProductVendorppv)。The query optimizer joins Product and ProductVendor (p and pv) by using a MERGE join. 然后,ProductProductVendor MERGE 联接(ppv)的结果被 HASH 联接到 Vendor 表,生成(ppv)和 vNext, 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.

重要

指定了联接提示后,要执行 INNER JOIN 时,INNER 关键字不再为可选,而必须显式说明。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.

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.I. 使用派生表Using a derived table

下面的示例使用派生表(SELECT 子句后的 FROM 语句)返回所有员工的名字和姓氏及其居住的城市。The following example uses a derived table, a SELECT statement after the FROM clause, to return the first and last names of all employees and the cities in which they live.

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.J. 使用 TABLESAMPLE 从表中的行样本中读取数据Using TABLESAMPLE to read data from a sample of rows in a table

下面的示例在 TABLESAMPLE 子句中使用 FROM,大约返回 10 表中所有行的 Customer%。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.K. 使用 APPLYUsing APPLY

以下示例假定数据库中存在以下表和表值函数:The following example assumes that the following tables and table-valued function exist in the database:

Object NameObject Name 列名Column Names
部门Departments DeptID、DivisionID、DeptName、DeptMgrIDDeptID, DivisionID, DeptName, DeptMgrID
EmpMgrEmpMgr MgrID、EmpIDMgrID, EmpID
EmployeesEmployees EmpID、EmpLastName、EmpFirstName、EmpSalaryEmpID, EmpLastName, EmpFirstName, EmpSalary
GetReports(MgrID)GetReports(MgrID) EmpID、EmpLastName、EmpSalaryEmpID, EmpLastName, EmpSalary

GetReports 表值函数返回直接或间接报告给指定 MgrID 的所有员工的列表。The GetReports table-valued function, returns the list of all employees that report directly or indirectly to the specified MgrID.

该示例使用 APPLY 返回所有部门和部门中的所有员工。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) ;  

如果您希望查询为那些没有员工的部门生成行(这将为 EmpIDEmpLastNameEmpSalary 列生成 Null 值),请改用 OUTER APPLYIf you want the query to produce rows for those departments without employees, which will produce null values for the EmpID, EmpLastName and EmpSalary columns, use OUTER APPLY instead.

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

L.L. 使用 CROSS APPLYUsing CROSS APPLY

以下示例将检索驻留在计划缓存中的所有查询计划的快照,方法是通过查询 sys.dm_exec_cached_plans 动态管理视图来检索缓存中所有查询计划的计划句柄。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. 然后,指定 CROSS APPLY 运算符以将计划句柄传递给 sys.dm_exec_query_planThen the CROSS APPLY operator is specified to pass the plan handles to sys.dm_exec_query_plan. 当前在计划缓存中的每个计划的 XML 显示计划输出位于返回的表的 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.M. 使用 FOR SYSTEM_TIMEUsing FOR SYSTEM_TIME

适用范围:SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017 and SQL DatabaseSQL Database.

下面的示例使用 FOR SYSTEM_TIME AS OF date_time_literal_or_variable 参数返回自 2014 年 1 月 1 日起的活跃(最新)表行。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;

下面的示例使用 FOR SYSTEM_TIME FROM date_time_literal_or_variable TO date_time_literal_or_variable 参数返回定义期限(从 2013 年 1 月 1 日开始,到 2014 年 1 月 1 日截止,不包括上限时间)内的所有活跃行。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;

下面的示例使用 FOR SYSTEM_TIME BETWEEN date_time_literal_or_variable AND date_time_literal_or_variable 参数返回定义期限(从 2013 年 1 月 1 日开始,到 2014 年 1 月 1 日截止,包括上限时间)内的所有活跃行。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;

下面的示例使用 FOR SYSTEM_TIME CONTAINED IN ( date_time_literal_or_variable, date_time_literal_or_variable ) 参数返回定义期限(从 2013 年 1 月 1 日开始,到 2014 年 1 月 1 日截止,包括上限时间)内的所有开放和关闭的行。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;

下面的示例使用变量(而不是文本)为查询提供日期边界值。The following example uses a variable rather than a literal to provide the date boundary values for the query.

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;

示例:Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseExamples: Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse

N.N. 使用 INNER JOIN 语法Using the INNER JOIN syntax

下面的示例返回 FactInternetSalesDimProduct 表(两个表中的联接键 ProductKey 相互匹配)中的 SalesOrderNumberProductKeyEnglishProductName 列。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. SalesOrderNumberEnglishProductName 列分别仅存在于一个表中,因此无需指定具有这些列的表别名(如下所示);包含这些别名可提高可读性。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. 别名前的 AS 一词不是必需的,但为提高可读性和符合 ANSI 标准,建议使用该词。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;  

由于内部联接无需 INNER 关键词,此相同的查询可写为: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 AS fis 
JOIN DimProduct AS dp  
ON dp.ProductKey = fis.ProductKey;  

WHERE 子句还可能与此查询一起使用来限制结果。A WHERE clause could also be used with this query to limit results. 此示例将结果限制为高于“SO5000”的 SalesOrderNumber 值:This example limits results to SalesOrderNumber values higher than '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;  

O.O. 使用 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 语法Using the LEFT OUTER JOIN and RIGHT OUTER JOIN syntax

下面的示例联接 ProductKey 列上的 FactInternetSalesDimProduct 表。The following example joins the FactInternetSales and DimProduct tables on the ProductKey columns. 左外部联接语法保留左 (FactInternetSales) 表中的不匹配行。The left outer join syntax preserves the unmatched rows from the left (FactInternetSales) table. 由于 FactInternetSales 表不包含与 DimProduct 表匹配的任何 ProductKey 值,此查询将返回与上述的第一个内联示例相同的行。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 关键词。This query could also be written without the OUTER keyword.

在右外部联接中,右表中的不匹配行将会保留。In right outer joins, the unmatched rows from the right table are preserved. 下面的示例将返回与上述的左外部联接示例相同的行。The following example returns the same rows as the left outer join example above.

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

以下查询使用 DimSalesTerritory 表作为左外部联接中的左表。The following query uses the DimSalesTerritory table as the left table in a left outer join. 它从 FactInternetSales 表中检索 SalesOrderNumber 值。It retrieves the SalesOrderNumber values from the FactInternetSales table. 如果没有针对特定 SalesTerritoryKey 的顺序,该查询将针对该行中的 SalesOrderNumber 返回 NULL。If there are no orders for a particular SalesTerritoryKey, the query will return a NULL for the SalesOrderNumber for that row. 此查询将按照 SalesOrderNumber 列进行排序,以便此列中的任何 NULL 都会显示在结果顶部。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;  

可使用右外部联接改写此查询以检索相同结果:This query could be rewritten with a right outer join to retrieve the same results:

-- 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;  

P.P. 使用 FULL OUTER JOIN 语法Using the FULL OUTER JOIN syntax

下面的示例演示完全外部联接,它将返回两个联接表中的所有行,但会针对其他表中的不匹配值返回 NULL。The following example demonstrates a full outer join, which returns all rows from both joined tables but returns NULL for values that do not match from the other table.

-- 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 关键词。This query could also be written without the OUTER keyword.

-- 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;  

Q.Q. 使用 CROSS JOIN 语法Using the CROSS JOIN syntax

下面的示例返回 FactInternetSalesDimSalesTerritory 表的叉积。The following example returns the cross-product of the FactInternetSales and DimSalesTerritory tables. 包含所返回的 SalesOrderNumberSalesTerritoryKey 的所有可能组合的列表。A list of all possible combinations of SalesOrderNumber and SalesTerritoryKey are returned. 请注意,交叉联接查询中缺少 ON 子句。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;  

R.R. 使用派生表Using a derived table

下面的示例使用派生表(FROM 子句后的 SELECT 语句)返回 DimCustomer 表(具有 1970 年 1 月 1 日以后的 BirthDate 值和姓氏“Smith”)中所有客户的 CustomerKeyLastName 列。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;  

S.S. REDUCE 联接提示示例REDUCE join hint example

下面的示例使用 REDUCE 联接提示更改对查询内的派生表的处理。The following example uses the REDUCE join hint to alter the processing of the derived table within the query. 此查询中使用 REDUCE 联接提示时,fis.ProductKey 将被投影、复制和进行区别处理,然后在对 ProductKey 上的 DimProduct 执行 shuffle 时联接到 DimProductWhen 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. 生成的派生表将分布在 fis.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;  

T.T. REPLICATE 联接提示示例REPLICATE join hint example

本示例演示与上一示例相同的查询,但使用的是 REPLICATE 联接提示,而非 REDUCE 联接提示。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. 使用 REPLICATE 提示会导致 FactInternetSales 表中的 ProductKey(联接)列复制到所有节点。Use of the REPLICATE hint causes the values in the ProductKey (joining) column from the FactInternetSales table to be replicated to all nodes. DimProduct 表将与这些值的复制版本进行联接。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;  

U.U. 使用 REDISTRIBUTE 提示以保证对分布不兼容联接使用 Shuffle 移动Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible join

下面的查询针对分布不兼容联接使用 REDISTRIBUTE 查询提示。The following query uses the REDISTRIBUTE query hint on a distribution incompatible join. 这能确保查询优化器在查询计划中使用 Shuffle 移动。This guarantees the query optimizer will use a Shuffle move in the query plan. 这还可确保查询计划不会使用可将分布表移动到复制表的 Broadcast 移动。This also guarantees the query plan will not use a Broadcast move which moves a distributed table to a replicated table.

在下一示例中,REDISTRIBUTE 提示强制对 FactInternetSales 表执行 Shuffle 移动,因为 ProductKey 是 DimProduct 的分布列,但不是 FactInternetSales 的分布列。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 AS dp 
INNER REDISTRIBUTE JOIN FactInternetSales AS fis  
    ON dp.ProductKey = fis.ProductKey;  

V.V. 使用 TABLESAMPLE 从表中的行样本中读取数据Using TABLESAMPLE to read data from a sample of rows in a table

下面的示例在 TABLESAMPLE 子句中使用 FROM,大约返回 10 表中所有行的 Customer%。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) ;

另请参阅See Also

CONTAINSTABLE (Transact-SQL) CONTAINSTABLE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
FREETEXTTABLE (Transact-SQL) FREETEXTTABLE (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
OPENQUERY (Transact-SQL) OPENQUERY (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
运算符 (Transact-SQL) Operators (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)