Summary: in this tutorial, you will learn how to use the SQL Server INNER JOIN clause to query data from multiple tables. Introduction to SQL Server INNER JOIN. The inner join is one of the most commonly used joins in SQL Server. The inner join clause allows you to query data from two or more related tables. In this visual diagram, the SQL Server LEFT OUTER JOIN returns the shaded area: The SQL Server LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1. Here is an example of a LEFT OUTER JOIN in SQL Server (Transact-SQL). Summary: in this tutorial, you will learn about the SQL Server LEFT JOIN clause and how to use it to query data from multiple tables. Introduction to SQL Server LEFT JOIN clause. The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.
-->SQL Server performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. Using this type of query plan, SQL Server supports vertical table partitioning, sometimes called columnar storage.
SQL Server employs four types of join operations:
- Nested Loops joins
- Merge joins
- Hash joins
- Adaptive joins (Starting with SQL Server 2017 (14.x))
Join Fundamentals
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.
A join condition defines the way two tables are related in a query by:
- Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
- Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Inner joins can be specified in either the
FROM
or WHERE
clauses. Outer joins can be specified in the FROM
clause only. The join conditions combine with the WHERE
and HAVING
search conditions to control the rows that are selected from the base tables referenced in the FROM
clause.Specifying the join conditions in the
FROM
clause helps separate them from any other search conditions that may be specified in a WHERE
clause, and is the recommended method for specifying joins. A simplified ISO FROM clause join syntax is:join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition defines the predicate to be evaluated for each pair of joined rows. The following is an example of a FROM clause join specification:
The following is a simple SELECT statement using this join:
The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.
When multiple tables are referenced in a single query, all column references must be unambiguous. In the previous example, both the ProductVendor and Vendor table have a column named BusinessEntityID. Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name. All references to the Vendor columns in the example are qualified.
When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name. This is shown in the previous example. Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. The readability of the query is improved if all columns are qualified with their table names. The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names. The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:
The previous examples specified the join conditions in the FROM clause, which is the preferred method. The following query contains the same join condition specified in the WHERE clause:
The select list for a join can reference all the columns in the joined tables, or any subset of the columns. The select list is not required to contain columns from every table in the join. For example, in a three-table join, only one table can be used to bridge from one of the other tables to the third table, and none of the columns from the middle table have to be referenced in the select list.
Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).
When SQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted.
Columns used in a join condition are not required to have the same name or be the same data type. However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert. If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the
CAST
function. For more information about implicit and explicit conversions, see Data Type Conversion (Database Engine).Most queries using a join can be rewritten using a subquery (a query nested within another query), and most subqueries can be rewritten as joins. For more information about subqueries, see Subqueries.
Note
Tables cannot be joined directly on ntext, text, or image columns. However, tables can be joined indirectly on ntext, text, or image columns by using
For example,
In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a
SUBSTRING
.For example,
SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20)
performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2.In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a
WHERE
clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)
Understanding Nested Loops joins
If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons.
The nested loops join, also called nested iteration, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table.
In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search exploits an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. All these variants are considered by the Query Optimizer.
A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.
When the OPTIMIZED attribute of a Nested Loops join operator is set to True, it means that an Optimized Nested Loops (or Batch Sort) is used to minimize I/O when the inner side table is large, regardless of it being parallelized or not. The presence of this optimization in a given plan may not be very obvious when analyzing an execution plan, given the sort itself is a hidden operation. But by looking in the plan XML for the attribute OPTIMIZED, this indicates the Nested Loops join may try to reorder the input rows to improve I/O performance.
Understanding Merge joins
If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation. If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance. However, hash join operations are often much faster if the two input sizes differ significantly from each other.
The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate. The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join. In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.
Because each input is sorted, the Merge Join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, the lower-value row is discarded and another row is obtained from that input. This process repeats until all rows have been processed.
The merge join operation may be either a regular or a many-to-many operation. A many-to-many merge join uses a temporary table to store rows. If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.
If a residual predicate is present, all rows that satisfy the merge predicate evaluate the residual predicate, and only those rows that satisfy it are returned.
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
Understanding Hash joins
Hash joins can efficiently process large, unsorted, nonindexed inputs. They are useful for intermediate results in complex queries because:
- Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
- Query optimizers estimate only intermediate result sizes. Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.
The hash join allows reductions in the use of denormalization. Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Hash joins reduce the need to denormalize. Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design.
The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.
Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Moreover, a variant of the hash join can do duplicate removal and grouping, such as
SUM(salary) GROUP BY department
. These modifications use only one input for both the build and probe roles.The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.
In-Memory Hash Join
The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.
Grace Hash Join
If the build input does not fit in memory, a hash join proceeds in several steps. This is known as a grace hash join. Each step has a build phase and probe phase. Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. The hash join is then applied to each pair of partitioned files.
Recursive Hash Join
If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.
Note
If the build input is only slightly larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.
It is not always possible during optimization to determine which hash join is used. Therefore, SQL Server starts by using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.
If the Query Optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. The hash join makes sure that it uses the smaller overflow file as build input. This technique is called role reversal. Role reversal occurs inside the hash join after at least one spill to the disk.
Note
Role reversal occurs independent of any query hints or structure. Role reversal does not display in your query plan; when it occurs, it is transparent to the user.
Hash Bailout
The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.
Note
Recursive hash joins or hash bailouts cause reduced performance in your server. If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.
For more information about hash bailout, see Hash Warning Event Class.
Understanding Adaptive joins
Batch mode Adaptive Joins enable the choice of a Hash Join or Nested Loops join method to be deferred until after the first input has been scanned. The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. A query plan can therefore dynamically switch to a better join strategy during execution without having to be recompiled.
Tip
Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.
The runtime decision is based on the following steps:
- If the row count of the build join input is small enough that a Nested Loops join would be more optimal than a Hash join, the plan switches to a Nested Loops algorithm.
- If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash join.
The following query is used to illustrate an Adaptive Join example:
The query returns 336 rows. Enabling Live Query Statistics displays the following plan:
In the plan, note the following:
- A columnstore index scan used to provide rows for the Hash join build phase.
- The new Adaptive Join operator. This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. For this example, the threshold is 78 rows. Anything with >= 78 rows will use a Hash join. If less than the threshold, a Nested Loops join will be used.
- Since the query returns 336 rows, this exceeded the threshold and so the second branch represents the probe phase of a standard Hash join operation. Notice that Live Query Statistics shows rows flowing through the operators - in this case '672 of 672'.
- And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded. Notice that we see '0 of 336' rows displayed (the branch is unused).
Now contrast the plan with the same query, but when the Quantity value only has one row in the table:
The query returns one row. Enabling Live Query Statistics displays the following plan:
In the plan, note the following:
- With one row returned, the Clustered Index Seek now has rows flowing through it.
- And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.
Adaptive Join remarks
Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. The additional memory is requested as if the Nested Loops was a Hash join. There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.
Batch mode Adaptive joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.
If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. The operator does not re-read the outer reference rows again.
Tracking Adaptive join activity
The Adaptive Join operator has the following plan operator attributes:
Plan attribute | Description |
---|---|
AdaptiveThresholdRows | Shows the threshold use to switch from a hash join to nested loop join. |
EstimatedJoinType | What the join type is likely to be. |
ActualJoinType | In an actual plan, shows what join algorithm was ultimately chosen based on the threshold. |
The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.
Tip
Query Store captures and is able to force a batch mode Adaptive Join plan.
Adaptive join eligible statements
A few conditions make a logical join eligible for a batch mode Adaptive Join:
- The database compatibility level is 140 or higher.
- The query is a
SELECT
statement (data modification statements are currently ineligible). - The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
- The Hash join uses Batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
- The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).
Adaptive threshold rows
The following chart shows an example intersection between the cost of a Hash join versus the cost of a Nested Loops join alternative. At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.
Disabling Adaptive joins without changing the compatibility level
Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:
To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:
When enabled, this setting will appear as enabled in sys.database_scoped_configurations.To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:
Adaptive joins can also be disabled for a specific query by designating
DISABLE_BATCH_MODE_ADAPTIVE_JOINS
as a USE HINT query hint. For example:Note
A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.
6+ Best PCB Design Software Download Reviews. PCB Design software is used to create schematic designs. This prepares a sketch of the electrical connections and is used to create various projects in a relatively short period of time. These programs benefit hobbyists as well as those who require it. PCB Artist Includes Features Such as Multi-Page Schematics and Netlist Import. Advanced Circuits' printed circuit board design software is not only easy to use, it is absolutely the best free PCB layout software available! Our customers tell us one of the reasons they love doing business with us is because of our PCB design software. Eagle pcb software download. FREE Software for Designing Circuit Boards Pad2Pad is free PCB layout software connected to a REAL circuit board manufacturer. It’s easy-to-learn, powerful, and includes instant pricing. Consult The Sunstone Circuits Printed Circuit Board Downloads And Materials For The Latest Information On PCB Prototype To Production. PCB Creator is a free, PCB layout and schematic capture software for both commercial and non-profit purposes. PCB Creator can be used to design 2 to 4 layer PCBs with 1000 pins or less and includes a 4-in-1 design environment including Schematic, PCB Layout with Autorouter, Component and Pattern Editors and 3D PCB Preview. An upgrade path is offered to the popular DipTrace software which.
Null Values and Joins
When there are null values in the columns of the tables being joined, the null values do not match each other. The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the
WHERE
clause excludes null values).Here are two tables that each have NULL in the column that will participate in the join:
A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:
Only one row with 4 in column a and c is returned:
Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. For example, the following
SELECT
statement does a left outer join on these two tables:Here is the result set.
The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.
See Also
Showplan Logical and Physical Operators Reference
Comparison Operators (Transact-SQL)
Data Type Conversion (Database Engine)
Subqueries
Adaptive Joins
-->Comparison Operators (Transact-SQL)
Data Type Conversion (Database Engine)
Subqueries
Adaptive Joins
SQL ServerSQL Serveresegue operazioni di ordinamento, intersezione, unione e differenza tramite le tecnologie di ordinamento in memoria e di hash join.performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology.Grazie a questo tipo di piano di query, SQL ServerSQL Server supporta il partizionamento verticale delle tabelle, denominato talvolta 'archiviazione a colonne'.Using this type of query plan, SQL ServerSQL Server supports vertical table partitioning, sometimes called columnar storage.
SQL ServerSQL Serverutilizza quattro tipi di operazioni di join:employs four types of join operations:
- Join a cicli annidatiNested Loops joins
- Merge joinMerge joins
- Hash joinHash joins
- Join adattivi (a partire da SQL Server 2017 (14.x)SQL Server 2017 (14.x))Adaptive joins (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x))
Nozioni di base sui joinJoin Fundamentals
I join consentono di recuperare dati da due o più tabelle in base alle relazioni logiche esistenti tra le tabelle stesse.By using joins, you can retrieve data from two or more tables based on logical relationships between the tables.I join indicano la modalità d'uso dei dati di una tabella in SQL ServerSQL Server per la selezione di righe in un'altra tabella.Joins indicate how SQL ServerSQL Server should use data from one table to select the rows in another table.
Una condizione di join definisce il modo in cui due tabelle sono correlate in una query in base agli elementi seguenti:A join condition defines the way two tables are related in a query by:
- L'impostazione della colonna di ogni tabella da utilizzare per il join.Specifying the column from each table to be used for the join.In una condizione di join tipica viene specificata una chiave esterna di una tabella e la chiave associata nell'altra tabella.A typical join condition specifies a foreign key from one table and its associated key in the other table.
- L'impostazione dell'operatore logico (ad esempio = o <>,) da usare per il confronto dei valori delle colonne.Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.
Gli inner join possono essere specificati nelle clausole
FROM
o WHERE
.Inner joins can be specified in either the FROM
or WHERE
clauses.Gli outer join possono essere specificati solo nella clausola FROM
.Outer joins can be specified in the FROM
clause only.Le condizioni di join vengono usate insieme alle condizioni di ricerca delle clausole WHERE
e HAVING
per definire le righe da selezionare nelle tabelle di base a cui viene fatto riferimento nella clausola FROM
.The join conditions combine with the WHERE
and HAVING
search conditions to control the rows that are selected from the base tables referenced in the FROM
clause.L'impostazione delle condizioni di join nella clausola
FROM
consente di separare queste condizioni da altre condizioni di ricerca specificate nella clausola WHERE
. Corrisponde anche al metodo consigliato per l'impostazione dei join.Specifying the join conditions in the FROM
clause helps separate them from any other search conditions that may be specified in a WHERE
clause, and is the recommended method for specifying joins.La sintassi ISO semplificata per la definizione di un join nella clausola FROM è la seguente:A simplified ISO FROM clause join syntax is:join_type specifica il tipo di join da eseguire: inner, outer o cross join.join_type specifies what kind of join is performed: an inner, outer, or cross join.join_condition definisce il predicato da valutare per ogni coppia di righe unite in join.join_condition defines the predicate to be evaluated for each pair of joined rows.Di seguito è riportato un esempio di definizione di join nella clausola FROM:The following is an example of a FROM clause join specification:
L'istruzione seguente è un'istruzione SELECT semplice in cui viene utilizzato tale join:The following is a simple SELECT statement using this join:
L'istruzione SELECT restituisce informazioni sul prodotto e sul fornitore per ogni combinazione di prodotti con prezzo maggiore di $ 10 fornito da una società il cui nome inizia con la lettera F.The select returns the product and supplier information for any combination of parts supplied by a company for which the company name starts with the letter F and the price of the product is more than $10.
Se in una singola query viene fatto riferimento a più tabelle, nessuno dei riferimenti alle colonne deve presentare ambiguità.When multiple tables are referenced in a single query, all column references must be unambiguous.Nell'esempio precedente, sia la tabella ProductVendor che la tabella Vendor avevano una colonna denominata BusinessEntityID.In the previous example, both the ProductVendor and Vendor table have a column named BusinessEntityID.I nomi di colonna duplicati in due o più tabelle a cui viene fatto riferimento nella query devono essere qualificati con il nome della tabella.Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name.Nell'esempio tutti i riferimenti alle colonne Vendor sono qualificati.All references to the Vendor columns in the example are qualified.
Se il nome di una colonna non è duplicato in due o più tabelle utilizzate nella query, non è necessario qualificare i riferimenti con il nome della tabella,When a column name is not duplicated in two or more tables used in the query, references to it do not have to be qualified with the table name.come illustrato nell'esempio precedente.This is shown in the previous example.Un'istruzione SELECT di questo tipo a volte è di difficile comprensione in quanto non indica la tabella a cui appartiene ogni colonna.Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column.La query risulta più leggibile se tutte le colonne sono qualificate con i nomi delle rispettive tabelle.The readability of the query is improved if all columns are qualified with their table names.Il grado di leggibilità aumenta ulteriormente se si utilizzano gli alias di tabella, soprattutto quando è necessario qualificare anche i nomi delle tabelle con il nome del database e del proprietario.The readability is further improved if table aliases are used, especially when the table names themselves must be qualified with the database and owner names.L'esempio seguente equivale all'esempio precedente. Per rendere la query più leggibile, sono stati però assegnati alias alle tabelle e i nomi di colonna sono stati qualificati con gli alias di tabella:The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:
Nell'esempio precedente le condizioni di join sono specificate nella clausola FROM (metodo consigliato).The previous examples specified the join conditions in the FROM clause, which is the preferred method.Nella query seguente la stessa condizione di join è specificata nella clausola WHERE:The following query contains the same join condition specified in the WHERE clause:
Nell'elenco di selezione di un join è possibile fare riferimento a tutte le colonne delle tabelle unite in join o a qualsiasi subset delle colonne.The select list for a join can reference all the columns in the joined tables, or any subset of the columns.Nell'elenco di selezione non è necessario specificare le colonne di ogni tabella del join.The select list is not required to contain columns from every table in the join.Ad esempio, in un join tra tre tabelle è possibile utilizzare una sola tabella come collegamento tra le altre due e le colonne di tale tabella non devono essere necessariamente incluse nell'elenco di selezione.For example, in a three-table join, only one table can be used to bridge from one of the other tables to the third table, and none of the columns from the middle table have to be referenced in the select list.
Sebbene le condizioni di join includano in genere confronti di uguaglianza (=), è possibile specificare altri operatori di confronto o relazionali e altri predicati.Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates.Per altre informazioni, vedere Operatori di confronto (Transact-SQL) e WHERE (Transact-SQL).For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).
Durante l'elaborazione di join in SQL ServerSQL Server, il motore query sceglie il metodo di elaborazione di join più efficiente tra quelli possibili.When SQL ServerSQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join.L'esecuzione fisica di vari join può essere realizzata con molte ottimizzazioni diverse e pertanto non può essere stimata in maniera affidabile.The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted.
Non è necessario che alle colonne di una condizione di join sia associato lo stesso nome o lo stesso tipo di dati.Columns used in a join condition are not required to have the same name or be the same data type.Se tuttavia i tipi di dati sono diversi, devono essere compatibili o supportare la conversione implicita di SQL Server.However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert.Se non è possibile eseguire la conversione implicita dei tipi di dati, è necessario impostare nella condizione di join la conversione esplicita tramite la funzione
CAST
.If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST
function.Per altre informazioni sulla conversione implicita ed esplicita dei dati, vedere Conversione di tipi di dati (motore di database).For more information about implicit and explicit conversions, see Data Type Conversion (Database Engine).La maggior parte delle query che includono un join possono essere riformulate specificando una subquery, ovvero una query nidificata in un'altra query. La maggior parte delle subquery possono a loro volta essere riformulate come join.Most queries using a join can be rewritten using a subquery (a query nested within another query), and most subqueries can be rewritten as joins.Per altre informazioni sulle sottoquery, vedere Subqueries (Sottoquery).For more information about subqueries, see Subqueries.
Nota
Non è possibile unire le tabelle in join direttamente in base alle colonne di tipo ntext, text o image.Tables cannot be joined directly on ntext, text, or image columns.Tuttavia, è possibile unire le tabelle in join indirettamente in base alle colonne di tipo ntext, text o image usando
Ad esempio,
È possibile anche confrontare colonne di tipo ntext e text di due tabelle confrontando la lunghezza delle colonne con una clausola
SUBSTRING
.However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING
.Ad esempio,
SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20)
esegue un inner join tra due tabelle sui primi 20 caratteri di ogni colonna di tipo text delle tabelle t1 e t2.For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20)
performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2.È possibile anche confrontare colonne di tipo ntext e text di due tabelle confrontando la lunghezza delle colonne con una clausola
WHERE
, come illustrato nell'esempio seguente: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)
In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE
clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)
Informazioni sui join a cicli annidatiUnderstanding Nested Loops joins
Se l'input di un join è ridotto (inferiore a 10 righe) e l'input dell'altro join è molto esteso e indicizzato in base alle rispettive colonne di join, l'operazione di join più rapida è rappresentata dai nested loop join indicizzati poiché questi richiedono la minore quantità di I/O e il minor numero di operazioni di confronto.If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons.
Il join a cicli annidati, detto anche iterazione nidificata, usa un input di join come tabella di input esterna, visualizzata come input superiore nel piano di esecuzione grafico, e un input di join come tabella di input interna (inferiore).The nested loops join, also called nested iteration, uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table.Il ciclo esterno elabora la tabella di input esterna, una riga alla volta.The outer loop consumes the outer input table row by row.Il ciclo interno, eseguito per ogni riga esterna, cerca le righe corrispondenti nella tabella di input interna.The inner loop, executed for each outer row, searches for matching rows in the inner input table.
Nel caso più semplice, ovvero un join a cicli annidati di tipo naive, la ricerca comporta l'analisi di un'intera tabella o indice.In the simplest case, the search scans an entire table or index; this is called a naive nested loops join.Nel caso di un join a cicli annidati indicizzato, per la ricerca viene usato un indice.If the search exploits an index, it is called an index nested loops join.In un join a cicli annidati temporaneo l'indice viene creato nell'ambito del piano di query ed eliminato dopo il completamento della query.If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join.Tutte queste varianti vengono elaborate da Query Optimizer.All these variants are considered by the Query Optimizer.
Un nested loop join è particolarmente efficace se l'input esterno è di dimensioni ridotte mentre l'input interno è preindicizzato e di dimensioni notevoli.A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large.In molte transazioni di dimensioni ridotte, ad esempio quelle relative a set di righe limitati, i nested loop join indicizzati offrono prestazioni superiori rispetto ai merge join e agli hash join.In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins.Per le query di notevoli dimensioni, tuttavia, i nested loop join rappresentano raramente la scelta ottimale.In large queries, however, nested loops joins are often not the optimal choice.
Quando l'attributo OPTIMIZED dell'operatore di join a cicli annidati viene impostato su True, significa che vengono usati cicli annidati ottimizzati (o l'ordinamento in batch) per ridurre al minimo le operazioni di I/O quando la tabella interna è di grandi dimensioni, indipendentemente dal fatto che sia parallelizzata o meno.When the OPTIMIZED attribute of a Nested Loops join operator is set to True, it means that an Optimized Nested Loops (or Batch Sort) is used to minimize I/O when the inner side table is large, regardless of it being parallelized or not.La presenza di questa ottimizzazione in un determinato piano potrebbe non essere ovvia durante l'analisi di un piano di esecuzione, dato che l'ordinamento stesso è un'operazione nascosta.The presence of this optimization in a given plan may not be very obvious when analyzing an execution plan, given the sort itself is a hidden operation.La presenza dell'attributo OPTIMIZED nel codice XML del piano, tuttavia, indica che il join a cicli annidati potrebbe tentare di riordinare le righe di input per migliorare le prestazioni di I/O.But by looking in the plan XML for the attribute OPTIMIZED, this indicates the Nested Loops join may try to reorder the input rows to improve I/O performance.
Informazioni sui merge joinUnderstanding Merge joins
Se i due input di join non sono di dimensioni ridotte, ma sono ordinati in base alla rispettiva colonna di join (ad esempio, se sono stati ottenuti tramite l'analisi di indici ordinati), l'operazione di join più rapida è rappresentata dal merge join.If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation.Se entrambi gli input di join sono di dimensioni notevoli e analoghe, un merge join con ordinamento eseguito in precedenza e un hash join offrono prestazioni simili.If both join inputs are large and the two inputs are of similar sizes, a merge join with prior sorting and a hash join offer similar performance.Le operazioni di hash join, tuttavia, risultano spesso molto più rapide se le dimensioni dei due input differiscono in modo significativo.However, hash join operations are often much faster if the two input sizes differ significantly from each other.
Per i merge join è necessario che entrambi gli input siano ordinati in base alle colonne di merge, definite dalle clausole di uguaglianza (ON) del predicato di join.The merge join requires both inputs to be sorted on the merge columns, which are defined by the equality (ON) clauses of the join predicate.In genere Query Optimizer esegue l'analisi di un indice, se questo esiste nel set di colonne, oppure inserisce un operatore di ordinamento sotto il merge join.The query optimizer typically scans an index, if one exists on the proper set of columns, or it places a sort operator below the merge join.In rari casi possono esistere più clausole di uguaglianza, ma le colonne di merge vengono ricavate soltanto da alcune delle clausole disponibili.In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.
Poiché ogni input è ordinato, l'operatore Merge Join recupera una riga da ogni input ed esegue il confronto tra le righe.Because each input is sorted, the Merge Join operator gets a row from each input and compares them.Ad esempio, per operazioni di inner join, le righe vengono restituite se sono uguali.For example, for inner join operations, the rows are returned if they are equal.In caso contrario, la riga con il valore minore viene scartata e dallo stesso input viene recuperata un'altra riga.If they are not equal, the lower-value row is discarded and another row is obtained from that input.Questo processo si ripete fino al completamento dell'elaborazione di tutte le righe.This process repeats until all rows have been processed.
L'operazione di merge join può essere un'operazione regolare o un'operazione molti-a-molti.The merge join operation may be either a regular or a many-to-many operation.Un merge join molti-a-molti utilizza una tabella temporanea per l'archiviazione delle righe.A many-to-many merge join uses a temporary table to store rows.Se sono presenti valori duplicati in entrambi gli input, uno degli input deve tornare all'inizio dei duplicati durante l'elaborazione di ogni duplicato dell'altro input.If there are duplicate values from each input, one of the inputs will have to rewind to the start of the duplicates as each duplicate from the other input is processed.
Se è presente un predicato residuo, tutte le righe conformi al predicato di merge vengono valutate da tale predicato e vengono restituite soltanto quelle che lo soddisfano.If a residual predicate is present, all rows that satisfy the merge predicate evaluate the residual predicate, and only those rows that satisfy it are returned.
Il merge join è di per sé un'operazione molto rapida, ma può essere una scelta onerosa se sono necessarie operazioni di ordinamento.Merge join itself is very fast, but it can be an expensive choice if sort operations are required.Se tuttavia il volume dei dati è elevato ed è possibile ottenere i dati desiderati già ordinati da indici ad albero B esistenti, il merge join risulta spesso l'algoritmo di join più veloce.However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
Informazioni sugli hash joinUnderstanding Hash joins
Gli hash join consentono l'elaborazione efficiente di input di grandi dimensioni, non ordinati e non indicizzati.Hash joins can efficiently process large, unsorted, nonindexed inputs.Tali join sono utili per ottenere risultati intermedi in query complesse, in quanto:They are useful for intermediate results in complex queries because:
- I risultati intermedi non sono indicizzati, a meno che non vengano salvati esplicitamente su disco e quindi indicizzati, e spesso non vengono ordinati in modo adeguato per l'operazione successiva nel piano di query.Intermediate results are not indexed (unless explicitly saved to disk and then indexed) and often are not suitably sorted for the next operation in the query plan.
- Query Optimizer stima esclusivamente le dimensioni dei risultati intermedi.Query optimizers estimate only intermediate result sizes.Poiché le stime relative a query complesse possono essere estremamente imprecise, è necessario non solo che gli algoritmi di elaborazione dei risultati intermedi siano efficienti, ma anche che vengano ridotti gradualmente nel caso in cui un risultato intermedio risulti molto più grande del previsto.Because estimates can be very inaccurate for complex queries, algorithms to process intermediate results not only must be efficient, but also must degrade gracefully if an intermediate result turns out to be much larger than anticipated.
Gli hash join consentono di ridurre la denormalizzazione.The hash join allows reductions in the use of denormalization.In genere, la denormalizzazione viene utilizzata per ottenere prestazioni migliori tramite la riduzione delle operazioni di join, nonostante rischi di ridondanza quali aggiornamenti non consistenti.Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates.Gli hash join riducono la necessità di utilizzo della denormalizzazione.Hash joins reduce the need to denormalize.Gli hash join rendono il partizionamento verticale (la rappresentazione di gruppi di colonne di un'unica tabella in file o indici separati) un'opzione adeguata per la progettazione fisica dei database.Hash joins allow vertical partitioning (representing groups of columns from a single table in separate files or indexes) to become a viable option for physical database design.
Gli hash join prevedono due tipi di input, ovvero l'input di compilazione e l'input probe.The hash join has two inputs: the build input and probe input.Query Optimizer assegna all'input più piccolo il ruolo di input di compilazione.The query optimizer assigns these roles so that the smaller of the two inputs is the build input.
Gli hash join vengono utilizzati per vari tipi di operazioni di corrispondenza tra set, ovvero inner join, outer join sinistro, destro e completo, semi-join sinistro e destro, intersezione, unione e differenza.Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference.Una variante dell'hash join può anche eseguire la rimozione dei duplicati e il raggruppamento, ad esempio
SUM(salary) GROUP BY department
.Moreover, a variant of the hash join can do duplicate removal and grouping, such as SUM(salary) GROUP BY department
.Queste modifiche utilizzano un solo input sia per il ruolo di compilazione che per il ruolo probe.These modifications use only one input for both the build and probe roles.Nelle sezioni seguenti vengono descritti i diversi tipi di hash join: hash join in memoria, grace hash join e hash join ricorsivo.The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.
Hash join in memoriaIn-Memory Hash Join
L'hash join esegue in primo luogo l'analisi o il calcolo dell'intero input di compilazione, quindi compila una tabella hash in memoria.The hash join first scans or computes the entire build input and then builds a hash table in memory.Ogni riga viene inserita in un hash bucket in base al valore hash calcolato per la chiave hash.Each row is inserted into a hash bucket depending on the hash value computed for the hash key.Se l'intero input di compilazione è inferiore alla memoria disponibile, tutte le righe possono essere inserite nella tabella hash.If the entire build input is smaller than the available memory, all rows can be inserted into the hash table.A questa fase di compilazione segue la fase probe.This build phase is followed by the probe phase.Viene eseguita l'analisi o il calcolo dell'intero input probe, una riga alla volta. Per ogni riga probe viene calcolato il valore della chiave hash, viene eseguita l'analisi dell'hash bucket corrispondente e vengono prodotte le corrispondenze.The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.
Grace hash joinGrace Hash Join
Se le dimensioni dell'input di compilazione sono superiori a quelle della memoria disponibile, l'hash join viene eseguito in vari passaggi.If the build input does not fit in memory, a hash join proceeds in several steps.In questo caso, l'hash join viene definito grace hash join.This is known as a grace hash join.Ogni passaggio prevede una fase di compilazione e una fase probe.Each step has a build phase and probe phase.L'input di compilazione e l'input probe vengono inizialmente analizzati e partizionati in più file, tramite una funzione di hashing sulle chiavi hash.Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files.L'utilizzo della funzione di hashing sulle chiavi hash garantisce che ogni coppia di record su cui si basa il join si trovi nella stessa coppia di file.Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files.In tal modo il join di due input di grandi dimensioni risulta convertito in più istanze di dimensioni ridotte della stessa attività.Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks.L'hash join viene quindi applicato a ogni coppia di file partizionati.The hash join is then applied to each pair of partitioned files.
Hash join ricorsivoRecursive Hash Join
Se l'input di compilazione ha dimensioni tali per cui gli input per un'unione esterna standard richiedono più livelli di unione, saranno necessari più passaggi di partizionamento e più livelli di partizionamento.If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required.Se soltanto alcune partizioni sono di grandi dimensioni, i passaggi di partizionamento aggiuntivi vengono utilizzati soltanto per tali partizioni.If only some of the partitions are large, additional partitioning steps are used for only those specific partitions.Per velocizzare al massimo i passaggi di partizionamento vengono utilizzate operazioni di I/O asincrone di grandi dimensioni, per cui un unico thread può tenere occupate più unità disco.In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.
Nota
Se l'input di compilazione non ha dimensioni di molto superiori a quelle della memoria disponibile, gli elementi dell'hash join in memoria e del grace hash join vengono combinati in un unico passaggio, producendo un hash join ibrido.If the build input is only slightly larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.
Durante l'ottimizzazione non sempre è possibile determinare il tipo di hash join che verrà utilizzato.It is not always possible during optimization to determine which hash join is used.Pertanto, SQL Server usa inizialmente un hash join in memoria, quindi passa gradualmente a grace hash join e hash join ricorsivo a seconda delle dimensioni dell'input di compilazione.Therefore, SQL Server starts by using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.
Se Query Optimizer non prevede correttamente quale dei due input è il più piccolo, al quale quindi deve essere assegnato il ruolo di input di compilazione, i ruoli di input di compilazione e di input probe vengono invertiti dinamicamente.If the Query Optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically.L'hash join utilizza comunque come input di compilazione il file di overflow più piccolo.The hash join makes sure that it uses the smaller overflow file as build input.Questa tecnica è detta inversione dei ruoli.This technique is called role reversal.L'inversione dei ruoli si verifica all'interno dell'hash join dopo l'esecuzione di almeno uno spill sul disco.Role reversal occurs inside the hash join after at least one spill to the disk.
Nota
L'inversione dei ruoli si verifica in maniera indipendente da qualsiasi struttura o hint per la queryRole reversal occurs independent of any query hints or structure.e inoltre non viene visualizzata nel piano di query. Quando si verifica, l'inversione dei ruoli è un'operazione trasparente all'utente.Role reversal does not display in your query plan; when it occurs, it is transparent to the user.
hash bailoutHash Bailout
Il termine hash bailout è talvolta usato per descrivere grace hash join o hash join ricorsivi.The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.
Nota
Gli hash join ricorsivi e gli hash bailout causano una riduzione delle prestazioni del server.Recursive hash joins or hash bailouts cause reduced performance in your server.Se si nota la presenza di molti eventi di avviso di hash in una traccia, aggiornare le statistiche sulle colonne che si sta unendo in join.If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.
Per altre informazioni sugli hash bailout, vedere Hash Warning - classe di evento.For more information about hash bailout, see Hash Warning Event Class.
Informazioni sui join adattiviUnderstanding Adaptive joins
Modalità batch I join adattivi consentono di rimandare a dopo la scansione del primo input la scelta tra l'esecuzione di un metodo hash join e l'esecuzione di un metodo join a cicli annidati.Batch mode Adaptive Joins enable the choice of a Hash Join or Nested Loops join method to be deferred until after the first input has been scanned.L'operatore Join adattivo definisce una soglia che viene usata per stabilire quando passare a un piano Cicli annidati.The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan.Durante l'esecuzione, un piano di query può pertanto passare a una strategia di join più efficace senza dover essere ricompilato.A query plan can therefore dynamically switch to a better join strategy during execution without having to be recompiled.
Suggerimento
Questa funzionalità è ottimale per i carichi di lavoro con frequenti oscillazioni tra i volumi di input di join rilevati.Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.
La decisione in fase di esecuzione è basata sui passaggi seguenti:The runtime decision is based on the following steps:
- Se il conteggio delle righe dell'input del join di compilazione è così ridotto che un join a cicli annidati è preferibile a un hash join, il piano passa a un algoritmo a cicli annidati.If the row count of the build join input is small enough that a Nested Loops join would be more optimal than a Hash join, the plan switches to a Nested Loops algorithm.
- Se l'input del join di compilazione supera una determinata soglia di numero di righe, non si verifica alcun cambiamento e il piano continua con un hash join.If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash join.
La query seguente illustra un esempio di join adattivo:The following query is used to illustrate an Adaptive Join example:
La query restituisce 336 righe.The query returns 336 rows.Se si attiva Statistiche query dinamiche, viene visualizzato il piano seguente:Enabling Live Query Statistics displays the following plan:
Nel piano notare quanto segue:In the plan, note the following:
- Un'analisi dell'indice columnstore che specifica le righe per la fase di compilazione dell'hash join.A columnstore index scan used to provide rows for the Hash join build phase.
- Il nuovo operatore di join adattivo.The new Adaptive Join operator.L'operatore definisce la soglia usata per il passaggio a un piano Cicli annidati.This operator defines a threshold that is used to decide when to switch to a Nested Loops plan.In questo esempio la soglia corrisponde a 78 righe.For this example, the threshold is 78 rows.Se il risultato è >= 78 righe, verrà usato un hash join.Anything with >= 78 rows will use a Hash join.Se è inferiore alla soglia, verrà usato un join a cicli annidati.If less than the threshold, a Nested Loops join will be used.
- Dato che la query restituisce 336 righe, questa soglia viene superata e pertanto il secondo ramo rappresenta la fase di probe di un'operazione hash join standard.Since the query returns 336 rows, this exceeded the threshold and so the second branch represents the probe phase of a standard Hash join operation.Si noti che Statistiche query dinamiche visualizza le righe del flusso tra gli operatori, in questo caso '672 di 672'.Notice that Live Query Statistics shows rows flowing through the operators - in this case '672 of 672'.
- L'ultimo ramo è una Ricerca indice cluster che il join a cicli annidati avrebbe usato se la soglia non fosse stata superata.And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded.Il valore visualizzato è '0 di 336' righe (il ramo non viene usato).Notice that we see '0 of 336' rows displayed (the branch is unused).
Confrontare ora il piano con la stessa query, ma in questo caso quando il valore Quantità ha una sola riga nella tabella:Now contrast the plan with the same query, but when the Quantity value only has one row in the table:
La query restituisce una riga.The query returns one row.Con l'abilitazione di Statistiche query dinamiche viene visualizzato il piano seguente:Enabling Live Query Statistics displays the following plan:
Nel piano notare quanto segue:In the plan, note the following:
- Con una sola riga restituita, ora il flusso di righe attraversa Ricerca indice cluster.With one row returned, the Clustered Index Seek now has rows flowing through it.
- Poiché non è stata portata avanti la fase di compilazione dell'hash join, nessuna riga attraversa il secondo ramo.And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.
Osservazioni per i join adattiviAdaptive Join remarks
I join adattivi presentano requisiti di memoria superiori rispetto a un piano equivalente con join a cicli annidati indicizzati.Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan.La memoria aggiuntiva risulta necessaria, come se il join a cicli annidati fosse un hash join.The additional memory is requested as if the Nested Loops was a Hash join.Si registra un sovraccarico anche per la fase di compilazione come operazione stop-and-go rispetto a un join a cicli annidati equivalente a livello di flussi.There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join.A tale costo aggiuntivo corrisponde una maggior flessibilità per gli scenari in cui i conteggi delle righe possono variare nell'input di compilazione.With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.
I join adattivi in modalità batch funzionano per l'esecuzione iniziale di un'istruzione. Dopo la compilazione, le esecuzioni consecutive restano adattive sulla base della soglia di join adattivo di compilazione e delle righe di runtime del flusso di dati della fase di compilazione dell'input esterno.Batch mode Adaptive joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.
Se un join adattivo passa al funzionamento con cicli annidati usa le righe già lette dalla compilazione hash join.If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build.L'operatore non legge di nuovo le righe del riferimento esterno.The operator does not re-read the outer reference rows again.
Rilevamento delle attività di join adattivoTracking Adaptive join activity
L'operatore Join adattivo ha i seguenti attributi dell'operatore del piano:The Adaptive Join operator has the following plan operator attributes:
Attributo del pianoPlan attribute | DescrizioneDescription |
---|---|
AdaptiveThresholdRowsAdaptiveThresholdRows | Visualizza l'uso della soglia che determina il passaggio da un hash join a un join a cicli annidati.Shows the threshold use to switch from a hash join to nested loop join. |
EstimatedJoinTypeEstimatedJoinType | Probabile tipo del join.What the join type is likely to be. |
ActualJoinTypeActualJoinType | In un piano reale visualizza l'algoritmo di join scelto in base alla soglia.In an actual plan, shows what join algorithm was ultimately chosen based on the threshold. |
Il piano stimato visualizza la struttura del piano di join adattivo, la soglia di join adattivo definita e il tipo di join stimato.The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.
Ms Sql Server Left Outer Join Syntax
Suggerimento
Query Store acquisisce e può imporre un piano di join adattivo in modalità batch.Query Store captures and is able to force a batch mode Adaptive Join plan.
Istruzioni idonee per i join adattiviAdaptive join eligible statements
Alcune condizioni rendono un join logico idoneo per un join adattivo in modalità batch:A few conditions make a logical join eligible for a batch mode Adaptive Join:
- Il livello di compatibilità del database è 140 o superiore.The database compatibility level is 140 or higher.
- La query è un'istruzione
SELECT
(attualmente le istruzioni di modifica dei dati non sono idonee).The query is aSELECT
statement (data modification statements are currently ineligible). - Il join è idoneo per l'esecuzione in un algoritmo fisico di join a cicli annidati indicizzati o di hash join.The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
- L'hash join usa la modalità batch con un indice columnstore nella query globale o una tabella Columnstore indicizzata a cui fa riferimento direttamente il join.The Hash join uses Batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
- Il primo elemento figlio (riferimento esterno) deve essere identico per le soluzioni alternative generate dal join a cicli annidati e dall'hash join.The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).
Righe della soglia adattivaAdaptive threshold rows
Il grafico seguente visualizza un esempio di intersezione tra il costo di un hash join e il costo di un join a cicli annidati alternativo.The following chart shows an example intersection between the cost of a Hash join versus the cost of a Nested Loops join alternative.In questo punto di intersezione viene determinata la soglia, che a sua volta determina l'algoritmo usato per l'operazione di join.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.
Disabilitazione dei join adattivi senza modificare il livello di compatibilitàDisabling Adaptive joins without changing the compatibility level
È possibile disabilitare i join adattivi nell'ambito del database o dell'istruzione mantenendo comunque la compatibilità sul livello 140 o superiore.Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
Per disabilitare i join adattivi per tutte le esecuzioni di query provenienti dal database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:
Per disabilitare i join adattivi per tutte le esecuzioni di query provenienti dal database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:
Quando è abilitata, questa impostazione viene visualizzata come abilitata in sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations.Per abilitare nuovamente i join adattivi per tutte le esecuzioni di query provenienti dal database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:
È anche possibile disabilitare i join adattivi per una query specifica, definendo
DISABLE_BATCH_MODE_ADAPTIVE_JOINS
come hint per la query USE HINT.Adaptive joins can also be disabled for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS
as a USE HINT query hint.Esempio:For example:Nota
L'hint per la query USE HINT ha la precedenza rispetto una configurazione con ambito database o un'impostazione del flag di traccia.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.
Valori Null e joinNull Values and Joins
Gli eventuali valori Null presenti nelle colonne delle tabelle da unire in join non possono essere associati ad altri valori.When there are null values in the columns of the tables being joined, the null values do not match each other.La presenza di valori Null in una colonna di una delle tabelle da unire in join viene restituita solo se si usa un outer join, a meno che la clausola
WHERE
non escluda i valori Null.The presence of null values in a column from one of the tables being joined can be returned only by using an outer join (unless the WHERE
clause excludes null values).Le due tabelle riportate di seguito includono entrambe un valore Null nella colonna interessata dal join:Here are two tables that each have NULL in the column that will participate in the join:
Un join che confronta i valori della colonna a con quelli della colonna c non consente di ottenere corrispondenze nelle colonne che includono valori NULL:A join that compares the values in column a against column c does not get a match on the columns that have values of NULL:
Nelle colonne a e c viene restituita una sola riga con valore 4:Only one row with 4 in column a and c is returned:
I valori Null restituiti da una tabella di base non sono inoltre facilmente distinguibili dai valori Null restituiti da un outer join.Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join.L'istruzione
SELECT
seguente, ad esempio, esegue un left outer join sull due tabelle seguenti:For example, the following SELECT
statement does a left outer join on these two tables:Set di risultati:Here is the result set.
Nel set di risultati non è agevole distinguere un valore NULL dei dati da un valore NULL che rappresenta un tentativo di join non riuscito.The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join.Se i dati da unire in join includono valori Null, è in genere consigliabile omettere tali valori dai risultati utilizzando un join normale.When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.
Vedere ancheSee Also
Guida di riferimento a operatori Showplan logici e fisiciShowplan Logical and Physical Operators Reference
Operatori di confronto (Transact-SQL)Comparison Operators (Transact-SQL)
Conversione di tipi di dati (motore di database)Data Type Conversion (Database Engine)
Subqueries (Sottoquery)Subqueries
Join adattiviAdaptive Joins
Operatori di confronto (Transact-SQL)Comparison Operators (Transact-SQL)
Conversione di tipi di dati (motore di database)Data Type Conversion (Database Engine)
Subqueries (Sottoquery)Subqueries
Join adattiviAdaptive Joins