FROM clause plus Bring together, Utilize, Pin (Transact-SQL)

  • Article
  • 30 minutes to read

Applies to: yesSQL Server 2016 (xiii.ten) and afterwards YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

In Transact-SQL, the FROM clause is available on the post-obit statements:

  • DELETE
  • UPDATE
  • SELECT

The FROM clause is usually required on the SELECT statement. The exception is when no tabular array columns are listed, and the but items listed are literals or variables or arithmetics expressions.

This commodity as well discusses the following keywords that can exist used on the FROM clause:

  • Bring together
  • APPLY
  • PIVOT

Topic link icon Transact-SQL Syntax Conventions

Syntax

              -- Syntax for SQL Server and Azure SQL Database      [ FROM { <table_source> } [ ,...due north ] ]    <table_source> ::=    {       table_or_view_name [ FOR SYSTEM_TIME <system_time> ] [ AS ] table_alias ]            [ <tablesample_clause> ]            [ WITH ( < table_hint > [ [ , ]...n ] ) ]        | rowset_function [ [ AS ] table_alias ]            [ ( bulk_column_alias [ ,...due north ] ) ]        | user_defined_function [ [ Equally ] 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 [ ,...northward ] )            [ [ AS ] table_alias ] [ (column_alias [ ,...due north ] ) ]      }   <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 } Utilize right_table_source        | [ ( ] <joined_table> [ ) ]    }   <join_type> ::=        [ { INNER | { { LEFT | Correct | FULL } [ OUTER ] } } [ <join_hint> ] ]       Bring together      <pivoted_table> ::=       table_source Pin <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> [ [ Every bit ] table_alias ]      <unpivot_clause> ::=       ( value_column FOR pivot_column IN ( <column_list> ) )       <column_list> ::=       column_name [ ,...n ]       <system_time> ::=   {          Equally 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 Synapse Analytics and Parallel Data Warehouse      FROM { <table_source> [ ,...n ] }      <table_source> ::=    {       [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ Equally ] table_or_view_alias      [<tablesample_clause>]       | derived_table [ AS ] table_alias [ ( column_alias [ ,...northward ] ) ]       | <joined_table>   }      <tablesample_clause> ::=     TABLESAMPLE ( sample_number [ PERCENT ] ) -- Azure Synapse Analytics Dedicated SQL puddle simply     <joined_table> ::=    {       <table_source> <join_type> <table_source> ON search_condition        | <table_source> CROSS Bring together <table_source>      | left_table_source { Cross | OUTER } APPLY right_table_source        | [ ( ] <joined_table> [ ) ]    }      <join_type> ::=        [ INNER ] [ <join hint> ] JOIN       | LEFT  [ OUTER ] JOIN       | Correct [ OUTER ] JOIN       | FULL  [ OUTER ] Join      <join_hint> ::=        REDUCE       | REPLICATE       | REDISTRIBUTE

Arguments

<table_source>
Specifies a table, view, tabular array variable, or derived tabular array source, with or without an alias, to apply in the Transact-SQL statement. Up to 256 table sources tin be used in a statement, although the limit varies depending on available retention and the complexity of other expressions in the query. Private queries may not support up to 256 table sources.

Notation

Query performance may suffer with lots of tables referenced in a query. Compilation and optimization fourth dimension 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 lodge of table sources after the FROM keyword does non affect the result set up 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 some other database on the same case of SQL Server, use a fully qualified proper noun in the course database.schema.object_name.

If the tabular array or view exists outside the instance of SQL Serverl, use a four-part proper noun in the form linked_server.itemize.schema.object. For more information, encounter sp_addlinkedserver (Transact-SQL). A four-part name that is synthetic past using the OPENDATASOURCE part 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 area to the capabilities of the OLE DB provider that accesses the remote object.

[Every bit] table_alias
Is an alias for table_source that can be used either for convenience or to distinguish a tabular array or view in a self-bring together or subquery. An alias is frequently a shortened table proper noun used to refer to specific columns of the tables in a bring together. If the same column name exists in more than one table in the join, SQL Server requires that the column name be qualified past a table name, view name, or alias. The table name cannot be used if an alias is divers.

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

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

rowset_function
Applies to: SQL Server 2008 and afterward and SQL Database.
Specifies 1 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 listing of rowset functions, run across 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
Applies to: SQL Server 2008 and later and SQL Database.
Is an optional allonym to supercede a column name in the result set. Column aliases are allowed only in SELECT statements that utilize the OPENROWSET function with the BULK option. When you employ bulk_column_alias, specify an alias for every table cavalcade in the same society as the columns in the file.

Note

This allonym overrides the Name aspect in the Column elements of an XML format file, if present.

user_defined_function
Specifies a table-valued part.

OPENXML <openxml_clause>
Applies to: SQL Server 2008 and later and SQL Database.
Provides a rowset view over an XML document. For more than 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 tabular array value constructor feature to specify multiple rows. For example, SELECT * FROM (VALUES (i, ii), (3, four), (5, 6), (7, eight), (ix, 10) ) AS MyTable(a, b);. For more information, see Tabular array Value Constructor (Transact-SQL).

column_alias
Is an optional alias to supplant a column proper name in the result ready of the derived table. Include one column allonym for each cavalcade in the select list, and enclose the complete listing of column aliases in parentheses.

table_or_view_name FOR SYSTEM_TIME <system_time> gpplies to**: SQL Server 2016 (13.10) and later and SQL Database.
Specifies that a specific version of information is returned from the specified temporal table and its linked system-versioned history table

Tablesample clause

Applies to: SQL Server, SQL Database Specifies that a sample of data from the tabular array is returned. The sample may be guess. This clause can be used on whatever principal or joined table in a SELECT or UPDATE argument. TABLESAMPLE cannot exist specified with views.

Note

When y'all apply TABLESAMPLE confronting databases that are upgraded to SQL Server, the compatibility level of the database is set to 110 or higher, PIVOT is not immune in a recursive common table expression (CTE) query. For more data, see ALTER DATABASE Compatibility Level (Transact-SQL).

Organization
Is an implementation-dependent sampling method specified by ISO standards. In SQL Server, this is the but sampling method available and is applied by default. Organisation applies a page-based sampling method in which a random set up 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 verbal or approximate abiding numeric expression that represents the per centum or number of rows. When specified with Percentage, sample_number is implicitly converted to a float value; otherwise, it is converted to bigint. Pct is the default.

Per centum
Specifies that a sample_number percent of the rows of the table should exist 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 exist 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 cipher.

REPEATABLE
Indicates that the selected sample can be returned again. When specified with the same repeat_seed value, SQL Server will render the same subset of rows equally long as no changes take been fabricated 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 adhere.

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

Tablesample clause

Applies to: Azure Synapse Analytics

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

PERCENT
Specifies that a sample_number percent of the rows of the table should be retrieved from the tabular array. When Percent is specified, Azure Synapse Analytics returns an approximate of the pct specified. When PERCENT is specified, the sample_number expression must evaluate to a value from 0 to 100.

Joined table

A joined table is a issue set that is the product of two or more tables. For multiple joins, use parentheses to change the natural gild 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 run into the bring together status is included in the result fix, and output columns that stand for to the other table are ready to Nothing. 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 status are included in the event set, and output columns from the other table are set to Nil in addition to all rows returned by the inner join.

RIGHT [OUTER]
Specifies all rows from the right table not meeting the bring together 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

For SQL Server and SQL Database, specifies that the SQL Server query optimizer use i join hint, or execution algorithm, per join specified in the query FROM clause. For more data, see Join Hints (Transact-SQL).

For Azure Synapse Analytics and Analytics Platform Arrangement (PDW), these join hints apply to INNER joins on 2 distribution incompatible columns. They can improve query performance past restricting the amount of data movement that occurs during query processing. The allowable join hints for Azure Synapse Analytics and Analytics Platform System (PDW) are as follows:

REDUCE
Reduces the number of rows to be moved for the table on the correct side of the join in order to make two distribution incompatible tables compatible. The REDUCE hint is also called a semi-join hint.

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

REDISTRIBUTE
Forces 2 data sources to exist distributed on columns specified in the Bring together clause. For a distributed tabular array, Analytics Platform Organisation (PDW) will perform a shuffle motion. For a replicated tabular array, Analytics Platform Organization (PDW) volition perform a trim movement. To empathize these movement types, see the "DMS Query Program Operations" section in the "Agreement Query Plans" topic in the Parallel Information Warehouse product documentation. This hint can improve performance when the query plan is using a broadcast move to resolve a distribution incompatible bring together.

JOIN
Indicates that the specified bring together functioning 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:

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

When the condition specifies columns, the columns practice not take to have the aforementioned name or aforementioned data blazon; nonetheless, if the data types are not the same, they must exist either uniform 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 office.

There tin be predicates that involve only one of the joined tables in the ON clause. Such predicates also tin can be in the WHERE clause in the query. Although the placement of such predicates does not make a deviation for INNER joins, they might cause a unlike consequence when OUTER joins are involved. This is considering the predicates in the ON clause are applied to the tabular array earlier the join, whereas the WHERE clause is semantically practical to the consequence of the join.

For more information nigh search conditions and predicates, come across Search Condition (Transact-SQL).

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

left_table_source { CROSS | OUTER } Use 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 tabular array-valued part that takes column values from the left_table_source equally one of its arguments.

Either Cantankerous or OUTER must be specified with APPLY. When Cantankerous 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 outcome set.

When OUTER is specified, i 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 ready.

For more than information, run into the Remarks section.

left_table_source
Is a tabular array source equally defined in the previous argument. For more information, see the Remarks department.

right_table_source
Is a table source as divers in the previous argument. For more information, meet the Remarks department.

Pin clause

table_source PIVOT <pivot_clause>
Specifies that the table_source is pivoted based on the pivot_column. table_source is a tabular array 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. For more data about Pin and UNPIVOT, run into Using PIVOT and UNPIVOT.

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

For more information, run into the Remarks department that follows.

aggregate_function
Is a organization or user-defined amass part that accepts 1 or more than inputs. The amass function should be invariant to null values. An aggregate function invariant to aught values does not consider null values in the group while it is evaluating the aggregate value.

The COUNT(*) arrangement aggregate role is not immune.

value_column
Is the value column of the Pivot operator. When used with UNPIVOT, value_column cannot be the proper noun 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 blazon implicitly or explicitly convertible to nvarchar(). This cavalcade cannot exist prototype or rowversion.

When UNPIVOT is used, pivot_column is the proper 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 Pin clause, lists the values in the pivot_column that will become the cavalcade names of the output table. The list cannot specify any cavalcade names that already be in the input table_source that is being pivoted.

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

table_alias
Is the allonym 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 chosen pivot_column. For more data most Pin and UNPIVOT, meet Using Pin and UNPIVOT.

AS OF <date_time>
Applies to: SQL Server 2016 (13.ten) and subsequently and SQL 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. Internally, a spousal relationship is performed between the temporal table and its history table and the results are filtered to render the values in the row that was valid at the point in fourth dimension specified by the <date_time> parameter. 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> Applies to: SQL Server 2016 (13.x) and later and SQL Database. Returns a table with the values for all record versions that were agile within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM statement or ceased beingness active after the <end_date_time> parameter value for the TO argument. Internally, a union is performed between the temporal tabular array 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 fourth dimension range specified. Rows that became active exactly on the lower boundary divers by the FROM endpoint are included and rows that became active exactly on the upper purlieus divers by the TO endpoint are not included.

Between <start_date_time> AND <end_date_time>
Applies to: SQL Server 2016 (thirteen.x) and later and SQL Database.
Same as above in the FROM <start_date_time> TO <end_date_time> clarification, except it includes rows that became active on the upper boundary divers by the <end_date_time> endpoint.

Independent IN (<start_date_time> , <end_date_time>)
Applies to: SQL Server 2016 (13.x) and later and SQL Database.
Returns a tabular array with the values for all record versions that were opened and closed inside 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.

ALL
Returns a tabular array with the values from all rows from both the electric current table and the history table.

Remarks

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 bring together operators.

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

A cocky-join is a table that is joined to itself. Insert or update operations that are based on a cocky-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-bring together strategy is appropriate. REMOTE bring together hint is useful for providers that do not provide column distribution statistics.

Using APPLY

Both the left and right operands of the Employ operator are tabular array expressions. The main divergence between these operands is that the right_table_source can use a tabular array-valued role that takes a cavalcade from the left_table_source as one of the arguments of the function. The left_table_source can include table-valued functions, but information technology cannot contain arguments that are columns from the right_table_source.

The Apply operator works in the following way to produce the table source for the FROM clause:

  1. Evaluates right_table_source against each row of the left_table_source to produce rowsets.

    The values in the right_table_source depend on left_table_source. right_table_source can be represented approximately this way: TVF(left_table_source.row), where TVF is a table-valued function.

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

    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.

Using PIVOT and UNPIVOT

The pivot_column and value_column are grouping columns that are used past the Pivot operator. PIVOT follows the following procedure to obtain the output result fix:

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

    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 post-obit:

    1. Group additionally the rows generated in the GROUP Past in the previous step against the pivot_column.

      For each output column in the column_list, selecting a subgroup that satisfies the condition:

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

    2. aggregate_function is evaluated against the value_column on this subgroup and its effect is returned as the value of the corresponding output_column. If the subgroup is empty, SQL Server generates a goose egg value for that output_column. If the aggregate role is COUNT and the subgroup is empty, nix (0) is returned.

Note

The cavalcade identifiers in the UNPIVOT clause follow the catalog collation. For SQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. For SQL Server partially independent databases, the collation is ever Latin1_General_100_CI_AS_KS_WS_SC. If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

For more than data almost PIVOT and UNPIVOT including examples, see Using Pivot and UNPIVOT.

Permissions

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

Examples

A. Using a simple FROM clause

The following instance retrieves the TerritoryID and Name columns from the SalesTerritory table in the AdventureWorks2012 sample database.

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

Here is the result set.

              TerritoryID Name                             ----------- ------------------------------   1           Northwest                        two           Northeast                        three           Cardinal                          four           Southwest                        five           Southeast                        half-dozen           Canada                           vii           French republic                           8           Deutschland                          nine           Australia                        10          United Kingdom                   (ten row(south) affected)

B. Using the TABLOCK and HOLDLOCK optimizer hints

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

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

C. Using the SQL-92 Cantankerous Bring together syntax

The post-obit instance returns the cross production of the two tables Employee and Section in the AdventureWorks2012 database. A list of all possible combinations of BusinessEntityID rows and all Section name rows are returned.

              SELECT e.BusinessEntityID, d.Name Equally Section   FROM HumanResources.Employee AS eastward   CROSS JOIN HumanResources.Section AS d   ORDER BY eastward.BusinessEntityID, d.Proper noun ;

D. Using the SQL-92 FULL OUTER Join syntax

The post-obit case returns the product name and whatever 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 Total keyword is optional.   SELECT p.Name, sod.SalesOrderID   FROM Product.Production As p   FULL OUTER JOIN Sales.SalesOrderDetail As sod   ON p.ProductID = sod.ProductID   ORDER By p.Proper noun ;

Due east. Using the SQL-92 LEFT OUTER JOIN syntax

The post-obit example joins two tables on ProductID and preserves the unmatched rows from the left table. The Product tabular array is matched with the SalesOrderDetail table on the ProductID columns in each table. All products, ordered and non ordered, appear in the result set.

              SELECT p.Name, sod.SalesOrderID   FROM Product.Production AS p   LEFT OUTER Join Sales.SalesOrderDetail AS sod   ON p.ProductID = sod.ProductID   Club By p.Proper noun ;

F. Using the SQL-92 INNER JOIN syntax

The post-obit example returns all product names and sales order IDs.

              -- Past default, SQL Server performs an INNER Bring together if only the Join    -- keyword is specified.   SELECT p.Proper noun, sod.SalesOrderID   FROM Production.Production AS p   INNER Bring together Sales.SalesOrderDetail Equally sod   ON p.ProductID = sod.ProductID   Society BY p.Proper name ;

G. Using the SQL-92 Correct OUTER JOIN syntax

The following instance joins two tables on TerritoryID and preserves the unmatched rows from the right table. The SalesTerritory table is matched with the SalesPerson table on the TerritoryID column in each tabular array. All salespersons announced in the result set, whether or not they are assigned a territory.

              SELECT st.Name AS Territory, sp.BusinessEntityID   FROM Sales.SalesTerritory AS st    Correct OUTER JOIN Sales.SalesPerson Every bit sp   ON st.TerritoryID = sp.TerritoryID ;

H. Using HASH and MERGE join hints

The following case performs a three-tabular array join amongst the Product, ProductVendor, and Vendor tables to produce a list of products and their vendors. The query optimizer joins Production 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 tabular array to produce (p and pv) and five.

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.

              SELECT p.Name AS ProductName, v.Proper name AS VendorName   FROM Product.Product AS p    INNER MERGE Bring together Purchasing.ProductVendor Every bit 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

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

              SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Proper name, d.City   FROM Person.Person AS p   INNER Join HumanResources.Employee due east ON p.BusinessEntityID = e.BusinessEntityID    INNER Join      (SELECT bea.BusinessEntityID, a.City        FROM Person.Address AS a       INNER Bring together 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 information from a sample of rows in a table

The following example uses TABLESAMPLE in the FROM clause to render approximately 10 per centum of all the rows in the Customer table.

              SELECT *   FROM Sales.Customer TABLESAMPLE Arrangement (10 Pct) ;

K. Using Utilise

The following example assumes that the following tables and table-valued function exist in the database:

Object Proper noun Column Names
Departments DeptID, DivisionID, DeptName, DeptMgrID
EmpMgr MgrID, EmpID
Employees EmpID, EmpLastName, EmpFirstName, EmpSalary
GetReports(MgrID) EmpID, EmpLastName, EmpSalary

The GetReports tabular array-valued function, returns the listing of all employees that written report straight or indirectly to the specified MgrID.

The case uses Utilise to render all departments and all employees in that section. If a item department does not have any employees, there will not exist any rows returned for that section.

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

If yous want the query to produce rows for those departments without employees, which will produce null values for the EmpID, EmpLastName and EmpSalary columns, employ OUTER Use instead.

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

50. Using CROSS Employ

The following example retrieves a snapshot of all query plans residing in the plan cache, past querying the sys.dm_exec_cached_plans dynamic management view to call back 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 primary;   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

1000. Using FOR SYSTEM_TIME

Applies to: SQL Server 2016 (xiii.x) and later 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 Jan 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 menstruum divers as starting with January 1, 2013 and catastrophe with January ane, 2014, exclusive of the upper boundary.

              SELECT DepartmentNumber,        DepartmentName,        ManagerID,        ParentDepartmentNumber    FROM Section   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 render all rows that were agile during the period defined every bit starting with January 1, 2013 and catastrophe 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 Independent IN ( date_time_literal_or_variable, date_time_literal_or_variable ) argument to return all rows that were opened and closed during the period divers as starting with January 1, 2013 and catastrophe with Jan one, 2014.

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

The post-obit 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;

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

Due north. Using the INNER JOIN syntax

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 ane of the tables only, so it is not necessary to specify the table allonym with these columns, as is shown; these aliases are included for readability. The word AS earlier an alias proper name is not required merely 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 non required for inner joins, this same query could exist written as:

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

A WHERE clause could also be used with this query to limit results. This instance 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'   Society By fis.SalesOrderNumber;

O. Using the LEFT OUTER Bring together and RIGHT OUTER Bring together syntax

The following case joins the FactInternetSales and DimProduct tables on the ProductKey columns. The left outer bring together syntax preserves the unmatched rows from the left (FactInternetSales) tabular array. Since the FactInternetSales table does not contain whatsoever ProductKey values that practise not match the DimProduct table, this query returns the same rows every bit the first inner join case above.

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

This query could also be written without the OUTER keyword.

In right outer joins, the unmatched rows from the correct tabular array are preserved. The following example returns the aforementioned rows as the left outer bring together 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;

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

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

This query could be rewritten with a correct outer bring together to retrieve the same results:

              -- Uses AdventureWorks      SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber   FROM FactInternetSales Every bit fis  Correct OUTER Join DimSalesTerritory AS dst       ON fis.SalesTerritoryKey = dst.SalesTerritoryKey   ORDER BY fis.SalesOrderNumber;

P. Using the Full OUTER Join syntax

The post-obit example demonstrates a full outer join, which returns all rows from both joined tables simply returns NULL for values that exercise 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   Guild Past fis.SalesOrderNumber;

This query could also exist written without the OUTER keyword.

              -- Uses AdventureWorks      SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber   FROM DimSalesTerritory AS dst  Total Bring together FactInternetSales As fis       ON dst.SalesTerritoryKey = fis.SalesTerritoryKey   ORDER By fis.SalesOrderNumber;

Q. Using the Cantankerous Bring together syntax

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

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

R. Using a derived tabular array

The following example uses a derived table (a SELECT argument after the FROM clause) to return the CustomerKey and LastName columns of all customers in the DimCustomer tabular array 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') Every bit DimCustomerDerivedTable   WHERE LastName = 'Smith'   Society BY LastName;

S. REDUCE bring together hint instance

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

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

T. REPLICATE bring together hint example

This next example shows the aforementioned query equally the previous instance, except that a REPLICATE bring together hint is used instead of the REDUCE join hint. Utilise of the REPLICATE hint causes the values in the ProductKey (joining) cavalcade 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 Bring together FactInternetSales AS fis             ON dp.ProductKey = fis.ProductKey      ) AS dTable   Guild By SalesOrderNumber;

U. Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible bring together

The following query uses the REDISTRIBUTE query hint on a distribution incompatible join. This guarantees the query optimizer volition use a Shuffle motion in the query programme. This besides guarantees the query plan volition non use a Broadcast motion which moves a distributed table to a replicated table.

In the post-obit example, the REDISTRIBUTE hint forces a Shuffle move on the FactInternetSales table because ProductKey is the distribution column for DimProduct, and is non 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. Using TABLESAMPLE to read data from a sample of rows in a table

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

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

Come across Also

CONTAINSTABLE (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
INSERT (Transact-SQL)
OPENQUERY (Transact-SQL)
OPENROWSET (Transact-SQL)
Operators (Transact-SQL)
WHERE (Transact-SQL)