Joins (SQL Server)Joins (SQL Server)

GILT FÜR: JaSQL Server JaAzure SQL-Datenbank JaAzure Synapse Analytics (SQL DW) JaParallel Data Warehouse APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL DW) YesParallel Data Warehouse

SQL ServerSQL Server verwendet bei Sortier-, Schnittmengen- und Vereinigungsvorgängen sowie bei Vorgängen zum Feststellen von Unterschieden arbeitsspeicherinterne Sortierverfahren und Hashjointechniken.performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. Beim Verwenden dieses Abfrageplantyps unterstützt SQL ServerSQL Server die vertikale Tabellenpartitionierung, auch spaltenweise Speicherung genannt.Using this type of query plan, SQL ServerSQL Server supports vertical table partitioning, sometimes called columnar storage.

SQL ServerSQL Server unterstützt vier Typen von Joinvorgängen:employs four types of join operations:

  • Joins geschachtelter SchleifenNested Loops joins
  • ZusammenführungsjoinsMerge joins
  • HashjoinsHash joins
  • Adaptive Joins (beginnend mit SQL Server 2017 (14.x)SQL Server 2017 (14.x))Adaptive joins (Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x))

Grundlegende Informationen zu JoinsJoin Fundamentals

Mithilfe von Joins können Sie Daten aus zwei oder mehr Tabellen basierend auf logischen Beziehungen zwischen den Tabellen abrufen.By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins zeigen an, wie SQL ServerSQL Server Daten aus einer Tabelle zum Auswählen der Zeilen in einer anderen Tabelle verwenden soll.Joins indicate how SQL ServerSQL Server should use data from one table to select the rows in another table.

Eine Joinbedingung definiert die Beziehung zweier Tabellen in einer Abfrage auf folgende Art:A join condition defines the way two tables are related in a query by:

  • Sie gibt die Spalte aus jeder Tabelle an, die für den Join verwendet werden soll.Specifying the column from each table to be used for the join. Eine typische Joinbedingung gibt einen Fremdschlüssel aus einer Tabelle und den zugehörigen Schlüssel in der anderen Tabelle an.A typical join condition specifies a foreign key from one table and its associated key in the other table.
  • Sie gibt einen logischen Operator (z. B. = oder <>) an, der zum Vergleichen von Werten aus den Spalten verwendet wird.Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Innere Joins können in FROM- oder in WHERE-Klauseln angegeben werden.Inner joins can be specified in either the FROM or WHERE clauses. Äußere Joins können nur in der FROM-Klausel angegeben werden.Outer joins can be specified in the FROM clause only. Die Joinbedingungen in Verbindung mit WHERE- und HAVING-Suchbedingungen steuern, welche Zeilen aus den Basistabellen ausgewählt werden, auf die in der FROM-Klausel verwiesen wird.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.

Das Angeben der Joinbedingungen in der FROM-Klausel trägt dazu bei, dass diese von anderen, möglicherweise in einer WHERE-Klausel angegebenen Suchbedingungen getrennt werden. Dies ist die empfohlene Methode zur Angabe von Joins.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. Das Folgende ist eine vereinfachte ISO-Joinssyntax für eine FROM-Klausel:A simplified ISO FROM clause join syntax is:

FROM first_table join_type second_table [ON (join_condition)]

join_type gibt an, welche Art von Join ausgeführt wird: innerer Join, äußerer Join oder Cross Join.join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition definiert das Prädikat, das für jedes verknüpfte Zeilenpaar ausgewertet werden soll.join_condition defines the predicate to be evaluated for each pair of joined rows. Es folgt ein Beispiel für eine Joinangabe in einer FROM-Klausel:The following is an example of a FROM clause join specification:

FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
     ON (ProductVendor.BusinessEntityID = Vendor.BusinessEntityID)

Es folgt eine einfache SELECT-Anweisung, die diesen Join verwendet:The following is a simple SELECT statement using this join:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'
GO

Die SELECT-Anweisung gibt die Produkt- und Lieferanteninformationen für alle Kombinationen von Teilen zurück, die von einer Firma geliefert werden, deren Firmenname mit dem Buchstaben F beginnt, und bei denen der Produktpreis über 10 $ liegt.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.

Wenn in einer einzigen Abfrage auf mehrere Tabellen verwiesen wird, müssen alle Spaltenverweise eindeutig sein.When multiple tables are referenced in a single query, all column references must be unambiguous. Im vorherigen Beispiel verfügen sowohl die ProductVendor-Tabelle als auch die Vendor-Tabelle über eine Spalte mit dem Namen „BusinessEntityID“.In the previous example, both the ProductVendor and Vendor table have a column named BusinessEntityID. Alle Spaltennamen, die in zwei oder mehr Tabellen vorkommen, auf die in der Abfrage verwiesen wird, müssen mit dem Tabellennamen gekennzeichnet werden.Any column name that is duplicated between two or more tables referenced in the query must be qualified with the table name. Alle Verweise auf die Vendor-Spalten im Beispiel sind gekennzeichnet.All references to the Vendor columns in the example are qualified.

Wenn ein Spaltenname nicht in zwei oder mehr in der Abfrage verwendeten Tabellen vorkommt, brauchen Verweise darauf nicht mit dem Tabellennamen gekennzeichnet zu werden.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. Dies ist im vorherigen Beispiel dargestellt.This is shown in the previous example. Eine solche SELECT-Anweisung ist manchmal schwer verständlich, weil nicht angezeigt wird, welche Spalte aus welcher Tabelle stammt.Such a SELECT statement is sometimes difficult to understand because there is nothing to indicate the table that provided each column. Die Lesbarkeit der Abfrage wird verbessert, indem alle Spalten mit dem entsprechenden Tabellennamen gekennzeichnet werden.The readability of the query is improved if all columns are qualified with their table names. Die Übersichtlichkeit kann weiterhin durch Verwenden von Tabellenaliasnamen verbessert werden, besonders, wenn die Tabellennamen mit dem Datenbank- und Besitzernamen gekennzeichnet werden müssen.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. Das folgende Beispiel unterscheidet sich vom vorherigen nur dadurch, dass Tabellenaliasnamen zugewiesen und die Spalten der Übersichtlichkeit halber mit Tabellenaliasnamen gekennzeichnet wurden:The following is the same example, except that table aliases have been assigned and the columns qualified with table aliases to improve readability:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv 
JOIN Purchasing.Vendor AS v
    ON (pv.BusinessEntityID = v.BusinessEntityID)
WHERE StandardPrice > $10
    AND Name LIKE N'F%';

In den vorhergehenden Beispielen wurden die Joinbedingungen in der FROM-Klausel angegeben. Dies ist die bevorzugte Methode.The previous examples specified the join conditions in the FROM clause, which is the preferred method. Die folgende Abfrage enthält dieselbe Joinbedingung, die in der WHERE-Klausel angegeben wird:The following query contains the same join condition specified in the WHERE clause:

SELECT pv.ProductID, v.BusinessEntityID, v.Name
FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v
WHERE pv.BusinessEntityID=v.BusinessEntityID
    AND StandardPrice > $10
    AND Name LIKE N'F%';

Die SELECT-Liste für einen Join kann auf alle Spalten in den verknüpften Tabellen oder auf eine beliebige Teilmenge der Spalten verweisen.The select list for a join can reference all the columns in the joined tables, or any subset of the columns. Es ist nicht erforderlich, dass die SELECT-Liste Spalten aus allen Tabellen in dem Join enthält.The select list is not required to contain columns from every table in the join. So kann z. B. in einem Join dreier Tabellen nur eine Tabelle verwendet werden, um die anderen Tabellen zu verbinden, und in der Auswahlliste muss auf keine der Spalten aus der mittleren Tabelle verwiesen werden.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.

Obwohl Joinbedingungen normalerweise Übereinstimmungsvergleiche (=) enthalten, können andere Vergleichsoperatoren oder relationale Operatoren ebenso wie andere Prädikate angegeben werden.Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. Weitere Informationen finden Sie unter Vergleichsoperatoren (Transact-SQL) und WHERE (Transact-SQL).For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).

Beim Verarbeiten von Joins durch SQL ServerSQL Server wählt die Abfrage-Engine aus verschiedenen Möglichkeiten die effizienteste Methode aus.When SQL ServerSQL Server processes joins, the query engine chooses the most efficient method (out of several possibilities) of processing the join. Die physische Ausführung von verschiedenen Joins kann viele verschiedene Optimierungen verwenden, weshalb keine zuverlässige Einschätzung möglich ist.The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted.

Spalten, die in einer Joinbedingung verwendet werden, müssen nicht den gleichen Namen oder Datentyp haben.Columns used in a join condition are not required to have the same name or be the same data type. Die Datentypen müssen jedoch kompatibel sein, falls sie nicht identisch sind, oder es müssen Datentypen sein, die SQL Server implizit konvertieren kann.However, if the data types are not identical, they must be compatible, or be types that SQL Server can implicitly convert. Wenn die Datentypen nicht implizit konvertiert werden können, muss die Joinbedingung den Datentyp mithilfe der CAST-Funktion explizit konvertieren.If the data types cannot be implicitly converted, the join condition must explicitly convert the data type using the CAST function. Weitere Informationen zur impliziten und expliziten Konvertierung finden Sie unter Datentypkonvertierung (Datenbank-Engine).For more information about implicit and explicit conversions, see Data Type Conversion (Database Engine).

Die meisten Abfragen, die einen Join verwenden, können in eine Unterabfrage (eine in eine andere Abfrage geschachtelte Abfrage) umgeschrieben werden, und die meisten Unterabfragen können in Joins umgeschrieben werden.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. Weitere Informationen zu Unterabfragen finden Sie unter Unterabfragen.For more information about subqueries, see Subqueries.

Hinweis

Tabellen können nicht direkt über ntext-, text- oder image-Spalten verknüpft werden.Tables cannot be joined directly on ntext, text, or image columns. Sie können jedoch mithilfe von SUBSTRING indirekt über ntext- , text- oder image-Spalten verknüpft werden.However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING.
SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) führt z.B. einen inneren Join zwischen zwei Tabellen auf den ersten 20 Zeichen jeder Textspalte in den Tabellen t1 und t2 aus.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.
Außerdem können ntext- oder text-Spalten aus zwei Tabellen verglichen werden, indem die Längen der Spalten mithilfe einer WHERE-Klausel wie im folgenden Beispiel verglichen werden: 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)

Grundlegendes zu Nested Loops-JoinsUnderstanding Nested Loops joins

Wenn eine Joineingabe klein (weniger als 10 Zeilen) und die andere Joineingabe relativ umfangreich ist und indizierte Joinspalten aufweist, ist ein indizierter Nested Loops-Join der schnellste Joinvorgang, da sie mit dem geringsten E/A-Aufkommen und den wenigsten Vergleichen auskommen.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.

Der Join geschachtelter Schleifen, auch geschachtelte Iteration genannt, verwendet eine Joineingabe als äußere Eingabetabelle (im grafischen Ausführungsplan als obere Eingabe dargestellt) und eine zweite Joineingabe als innere (untere) Eingabetabelle.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. Die äußere Schleife verarbeitet die äußere Eingabetabelle zeilenweise.The outer loop consumes the outer input table row by row. Die innere Schleife wird für jede äußere Zeile ausgeführt und sucht übereinstimmende Zeilen in der inneren Eingabetabelle.The inner loop, executed for each outer row, searches for matching rows in the inner input table.

Im einfachsten Fall wird beim Suchvorgang eine Tabelle oder ein Index vollständig gescannt. Dieser Vorgang wird als naiver Join geschachtelter Schleifen bezeichnet.In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. Wird beim Suchvorgang ein Index verwendet, wird dies indizierter Join geschachtelter Schleifen genannt.If the search exploits an index, it is called an index nested loops join. Wird der Index als Teil des Abfrageplans erstellt (und nach Beendigung der Abfrage gelöscht), wird dies temporärer indizierter Join geschachtelter Schleifen genannt.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. Alle beschriebenen Varianten werden vom Abfrageoptimierer berücksichtigt.All these variants are considered by the Query Optimizer.

Ein Nested Loops-Join ist besonders wirksam, wenn die äußere Eingabe klein und die innere Eingabe vorindiziert und umfangreich ist.A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. In vielen kleinen Transaktionen, die beispielsweise nur wenige Zeilen betreffen, sind indizierte Nested Loops-Joins sowohl Zusammenführungsjoins als auch Hashjoins überlegen.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 umfangreichen Abfragen dagegen sind Nested Loops-Joins häufig nicht die optimale Wahl.In large queries, however, nested loops joins are often not the optimal choice.

Wenn das „OPTIMIZED“-Attribut eines Operators für Joins geschachtelter Schleifen auf Truefestgelegt ist, bedeutet dies, dass ein optimierter Join geschachtelter Schleifen (oder eine Sortierung im Batchmodus) verwendet wird, um E/A-Vorgänge zu minimieren, wenn die innere Tabelle groß ist, unabhängig davon, ob sie parallelisiert wird oder nicht.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. Das Vorhandensein dieser Optimierung in einem bestimmten Plan ist beim Analysieren eines Ausführungsplans möglicherweise nicht offensichtlich, da die Sortierung selbst ein verborgener Vorgang ist.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. Wenn jedoch in der Plan-XML nach dem Attribut „OPTIMIZED“ gesucht wird, deutet dies darauf hin, dass der Join geschachtelter Schleifen möglicherweise versucht, die Eingabezeilen neu anzuordnen und die E/A-Leistung zu verbessern.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.

Grundlegendes zu ZusammenführungsjoinsUnderstanding Merge joins

Sind beide Joineingaben nicht klein, aber nach ihrer Joinspalte sortiert (was beispielsweise der Fall ist, wenn sie beim Scannen sortierter Indizes gewonnen wurden), so ist ein Zusammenführungsjoin der schnellste Joinvorgang.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. Sind beide Joineingaben umfangreich und etwa gleich groß, so bietet ein Zusammenführungsjoin mit vorherigem Sortiervorgang und ein Hashjoin vergleichbares Leistungsverhalten.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. Hashjoinvorgänge sind jedoch häufig erheblich schneller, wenn sich beide Eingaben im Umfang deutlich unterscheiden.However, hash join operations are often much faster if the two input sizes differ significantly from each other.

Der Zusammenführungsjoin setzt voraus, dass beide Eingaben nach den Zusammenführungsspalten sortiert sind; diese werden von den Gleichheitsoperatoren des Joinprädikats (in der ON-Klausel) definiert.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. Der Abfrageoptimierer scannt in der Regel einen Index, falls ein Index für die geeigneten Spalten vorhanden ist, oder platziert einen Sort-Operator unter den Zusammenführungsjoin.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 seltenen Fällen treten mehrere Gleichheitsklauseln auf, jedoch werden die Zusammenführungsspalten nur von einigen der verfügbaren Gleichheitsklauseln genommen.In rare cases, there may be multiple equality clauses, but the merge columns are taken from only some of the available equality clauses.

Da die Eingaben sortiert vorliegen, ruft der Merge Join-Operator von jeder Eingabe jeweils eine Zeile ab und vergleicht diese.Because each input is sorted, the Merge Join operator gets a row from each input and compares them. Beispielsweise werden bei inneren Joins die Zeilen zurückgegeben, wenn sie gleich sind.For example, for inner join operations, the rows are returned if they are equal. Sind sie nicht gleich, wird die Zeile mit dem kleineren Wert verworfen, und von der betreffenden Eingabe wird die nächste Zeile abgerufen.If they are not equal, the lower-value row is discarded and another row is obtained from that input. Dieser Vorgang wird wiederholt, bis alle Zeilen verarbeitet wurden.This process repeats until all rows have been processed.

Die Zusammenführungsjoinoperation kann eine reguläre oder eine n:n-Operation sein.The merge join operation may be either a regular or a many-to-many operation. Ein n:n-Zusammenführungsjoin verwendet eine temporäre Tabelle, um Zeilen zu speichern.A many-to-many merge join uses a temporary table to store rows. Treten bei den Eingaben doppelte Werte auf, so muss eine Eingabe bis zum ersten Duplikat zurückgespult werden, damit alle Duplikate der anderen Eingabe verarbeitet werden können.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.

Ist ein Residualprädikat vorhanden, so werten alle Zeilen, die dem Zusammenführungsprädikat genügen, das Residualprädikat aus. Nur die Zeilen werden zurückgegeben, die auch dem Residualprädikat genügen.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.

Der Zusammenführungsjoin ist sehr schnell, kann aber eine teure Wahl darstellen, wenn Sortieroperationen erforderlich sind.Merge join itself is very fast, but it can be an expensive choice if sort operations are required. Wenn jedoch die Datenmenge umfangreich ist und die gewünschten Daten vorsortiert aus vorhandenen B-Baum-Indizes abgerufen werden können, ist der Zusammenführungsjoin häufig der schnellste Joinalgorithmus.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.

Grundlegendes zu HashjoinsUnderstanding Hash joins

Hashjoins können umfangreiche, unsortierte, nicht indizierte Eingaben effizient verarbeiten.Hash joins can efficiently process large, unsorted, nonindexed inputs. Sie sind für Zwischenergebnisse in komplexen Abfragen aus folgenden Gründen nützlich:They are useful for intermediate results in complex queries because:

  • Zwischenergebnisse sind nicht indiziert (es sei denn, sie werden explizit auf einem Datenträger gespeichert und dann indiziert) und häufig nicht für den nächste Vorgang im Abfrageplan passend sortiert.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.
  • Abfrageoptimierer schätzen nur die Größe von Zwischenergebnissen ab.Query optimizers estimate only intermediate result sizes. Weil Schätzungen in komplexen Abfragen sehr ungenau sein können, müssen Algorithmen zum Verarbeiten von Zwischenergebnissen nicht nur effizient sein, sondern auch kontrolliert beendet werden können, wenn ein Zwischenergebnis viel umfangreicher als erwartet ausfällt.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.

Der Hashjoin lässt Reduktionen bei der Denormalisierung zu.The hash join allows reductions in the use of denormalization. Die Denormalisierung wird in der Regel verwendet, um bessere Leistung durch Reduzierung der Joinvorgänge zu erreichen, und zwar trotz der Redundanzgefahr wie beispielsweise durch inkonsistente Updates.Denormalization is typically used to achieve better performance by reducing join operations, in spite of the dangers of redundancy, such as inconsistent updates. Hashjoins vermindern den Bedarf, die Denormalisierung durchzuführen.Hash joins reduce the need to denormalize. Hashjoins ermöglichen die vertikale Partitionierung (die Darstellung von Spaltengruppen aus einer einzelnen Tabelle in separate Dateien oder Indizes), wodurch sie zu einer beachtenswerten Option für den physischen Datenbankentwurf wird.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.

Der Hashjoin verfügt über zwei Eingaben: die Erstellungseingabe und die Untersuchungseingabe.The hash join has two inputs: the build input and probe input. Der Abfrageoptimierer weist diese Rollen so zu, dass die kleinere Eingabe als Erstellungseingabe verwendet wird.The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

Hashjoins werden für viele ergebnismengenorientierte Operationen verwendet: INNER JOIN (innerer Join); LEFT, RIGHT und FULL OUTER JOIN (linker, rechter und vollständiger äußerer Join); LEFT SEMI-JOIN und RIGHT SEMI-JOIN (linker und rechter Semijoin); INTERSECTION (Schnittmenge); UNION (Vereinigungsmenge) und DIFFERENCE (Restmenge).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. Darüber hinaus können mit einer Variante des Hashjoins Duplikate entfernt und Gruppierungen vorgenommen werden, z.B. 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. Diese Varianten verwenden dieselbe Eingabe als Erstellungseingabe und Untersuchungseingabe.These modifications use only one input for both the build and probe roles.

In den folgenden Abschnitten werden verschiedene Typen von Hashjoins beschrieben: arbeitsspeicherinterne Hashjoins, schrittweise Hashjoins und rekursive Hashjoins.The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.

Arbeitsspeicherinterne HashjoinsIn-Memory Hash Join

Der Hashjoin scannt oder berechnet zuerst die gesamte Erstellungseingabe und erstellt dann eine Hashtabelle im Arbeitsspeicher.The hash join first scans or computes the entire build input and then builds a hash table in memory. Jede Zeile wird in ein Hashbucket eingefügt, abhängig von dem für den Hashschlüssel berechneten Hashwert.Each row is inserted into a hash bucket depending on the hash value computed for the hash key. Wenn die gesamte Erstellungseingabe kleiner als der verfügbare Arbeitsspeicher ist, können alle Zeilen in die Hashtabelle eingefügt werden.If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. Auf diese Erstellungsphase folgt die Untersuchungsphase.This build phase is followed by the probe phase. Die gesamte Untersuchungseingabe wird zeilenweise gescannt oder berechnet, und für jede Untersuchungszeile wird der Wert des Hashschlüssels berechnet, das entsprechende Hashbucket wird gescannt, und die Übereinstimmungen werden erzeugt.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-HashjoinsGrace Hash Join

Wenn die Erstellungseingabe nicht vollständig in den Arbeitsspeicher passt, wird der Hashjoin in mehreren Schritten durchgeführt.If the build input does not fit in memory, a hash join proceeds in several steps. Dies wird als schrittweiser Hashjoin bezeichnet.This is known as a grace hash join. Jeder Schritt besteht aus einer Erstellungsphase und einer Untersuchungsphase.Each step has a build phase and probe phase. Zuerst wird die gesamte Erstellungseingabe und Untersuchungseingabe verarbeitet und (durch Anwenden einer Hashfunktion auf die Hashschlüssel) in mehrere Dateien aufgeteilt.Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. Durch Anwenden der Hashfunktion auf die Hashschlüssel wird sichergestellt, dass die zwei zu verknüpfenden Datensätze sich stets in demselben Dateipaar befinden.Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. So wird die Aufgabe, zwei umfangreiche Eingaben zu verknüpfen, auf mehrere kleinere gleichartige Teilaufgaben reduziert.Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. Der Hashjoin wird dann auf jedes Paar partitionierter Dateien angewendet.The hash join is then applied to each pair of partitioned files.

Rekursive HashjoinsRecursive Hash Join

Wenn die Erstellungseingabe so umfangreich ist, dass Eingaben für einen standardmäßigen externen Mergeprozess mehrere Mergeebenen erfordern würden, sind mehrere Partitionierungsschritte und mehrere Partitionierungsebenen erforderlich.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. Sind nur einige Partitionen umfangreich, so sind zusätzliche Partitionierungsschritte nur für diese Partitionen erforderlich.If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. Um alle Partitionierungsschritte möglichst schnell zu machen, werden umfangreiche, asynchrone E/A-Operationen verwendet, sodass bereits ein Thread mehrere Datenträger auslastet.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.

Hinweis

Wenn die Erstellungseingabe nur wenig umfangreicher als der verfügbare Arbeitsspeicher ist, werden Elemente des schrittweisen und des arbeitsspeicherinternen Hashjoin in einem Schritt kombiniert, sodass ein hybrider Hashjoin entsteht.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.

Bei der Optimierung kann nicht in jedem Fall ermittelt werden, welcher Hashjoin verwendet wird.It is not always possible during optimization to determine which hash join is used. Daher verwendet SQL Server zunächst einen arbeitsspeicherinternen Hashjoin und geht, abhängig von der Größe der Erstellungseingabe, sukzessive zum GRACE- und rekursiven Hashjoin über.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.

Wenn der Abfrageoptimierer falsch einschätzt, welche Eingabe kleiner ist und daher als Erstellungseingabe verwendet werden müsste, werden die Rollen der Erstellungs- und der Untersuchungseingabe dynamisch vertauscht.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. Der Hashjoin stellt sicher, dass die kleinere Überlaufdatei als Erstellungseingabe verwendet wird.The hash join makes sure that it uses the smaller overflow file as build input. Diese Technik wird als Rollentausch bezeichnet.This technique is called role reversal. Ein Rollentausch tritt innerhalb des Hashjoins nach mindestens einem Überlauf auf den Datenträger auf.Role reversal occurs inside the hash join after at least one spill to the disk.

Hinweis

Der Rollentausch tritt unabhängig von Abfragehinweisen oder von der Struktur auf.Role reversal occurs independent of any query hints or structure. Der Rollentausch wird nicht im Abfrageplan angezeigt. Wenn ein solcher Vorgang auftritt, erfolgt er transparent für den Benutzer.Role reversal does not display in your query plan; when it occurs, it is transparent to the user.

HashabbruchHash Bailout

Der Begriff „Hashabbruch“ wird manchmal zur Beschreibung von GRACE- oder rekursiven Hashjoins verwendet.The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.

Hinweis

Rekursive Hashjoins oder Hashabbrüche verursachen eine reduzierte Leistung auf dem Server.Recursive hash joins or hash bailouts cause reduced performance in your server. Wenn in einer Ablaufverfolgung viele Hash Warning-Ereignisse angezeigt werden, sollten Sie die Statistiken für die verknüpften Spalten aktualisieren.If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.

Weitere Informationen zu Hashabbrüchen finden Sie unter Hash Warning-Ereignisklasse.For more information about hash bailout, see Hash Warning Event Class.

Grundlegendes zu adaptiven JoinsUnderstanding Adaptive joins

Mit dem Batchmodus für adaptive Joins können Sie wählen, ob eine Methode für Hashjoins oder Joins geschachtelter Schleifen zurückgestellt wird. Die Methode wird dann erst nach der Überprüfung der ersten Eingabe angewendet.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. Der Operator für adaptive Joins definiert einen Schwellenwert, der bestimmt, wann zu einem Plan geschachtelter Schleifen gewechselt wird.The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Daher kann ein Abfrageplan während der Ausführung dynamisch zu einer passenderen Joinstrategie wechseln, ohne dass er erneut kompiliert werden muss.A query plan can therefore dynamically switch to a better join strategy during execution without having to be recompiled.

Tipp

Workloads mit häufiger Oszillation zwischen kleinen und großen Joineingabescans profitieren am meisten von dieser Funktion.Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

Die Laufzeitentscheidung basiert auf den folgenden Schritten:The runtime decision is based on the following steps:

  • Wenn die Anzahl der Zeilen der Buildjoineingabe so klein ist, dass ein Join geschachtelter Schleifen passender wäre als ein Hashjoin, wechselt der Plan zu einem Algorithmus geschachtelter Schleifen.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.
  • Wenn die Buildjoineingabe eine bestimmte Anzahl an Zeilen übersteigt, wird nicht gewechselt, und der Plan wird mit einem Hashjoin fortgesetzt.If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash join.

Die folgende Abfrage veranschaulicht ein Beispiel für einen adaptiven Join:The following query is used to illustrate an Adaptive Join example:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;

Die Abfrage gibt 336 Zeilen zurück.The query returns 336 rows. Bei Aktivierung der Liveabfragestatistik wird der folgende Plan angezeigt:Enabling Live Query Statistics displays the following plan:

Abfrageergebnis: 336 Zeilen

Beachten Sie im Plan Folgendes:In the plan, note the following:

  1. Ein Columnstore-Indexscan wurde verwendet, um Zeilen für die Buildphase des Hashjoins bereitzustellen.A columnstore index scan used to provide rows for the Hash join build phase.
  2. Den neuen Operator für adaptive Joins.The new Adaptive Join operator. Dieser Operator definiert einen Schwellenwert, der bestimmt, wann zu einem Plan geschachtelter Schleifen gewechselt wird.This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. In diesem Beispiel entspricht der Schwellenwert 78 Zeilen.For this example, the threshold is 78 rows. Alles mit >= 78 Zeilen verwendet einen Hashjoin.Anything with >= 78 rows will use a Hash join. Wenn der Schwellenwert nicht überschritten wird, wird ein Join geschachtelter Schleifen verwendet.If less than the threshold, a Nested Loops join will be used.
  3. Da von der Abfrage 336 Zeilen zurückgegeben werden, wird der Schwellenwert überschritten. Deshalb stellt der zweite Branch die Überprüfungsphase eines standardmäßigen Hashjoinvorgangs dar.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. Beachten Sie, dass die Liveabfragestatistik Zeilen anzeigt, die die Operatoren durchlaufen: in diesem Fall „672 von 672“.Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
  4. Beim letzten Branch handelt es sich um eine Suche im gruppierten Index, die vom Join geschachtelter Schleifen verwendet worden wäre, wenn der Schwellenwert nicht überschritten worden wäre.And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded. Beachten Sie, dass „0 von 336“ Zeilen angezeigt werden (der Branch wird nicht verwendet).Notice that we see "0 of 336" rows displayed (the branch is unused).

Vergleichen Sie den Plan nun mit derselben Abfrage, dieses Mal aber mit einem Mengenwert, der nur eine Zeile in der Tabelle hat:Now contrast the plan with the same query, but when the Quantity value only has one row in the table:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;

Die Abfrage gibt eine Zeile zurück.The query returns one row. Bei Aktivierung der „Liveabfragestatistik“ wird der folgende Plan angezeigt:Enabling Live Query Statistics displays the following plan:

Abfrageergebnis eine Zeile

Beachten Sie im Plan Folgendes:In the plan, note the following:

  • Bei einer zurückgegebenen Zeile durchlaufen Zeilen jetzt den Clustered Index Seek.With one row returned, the Clustered Index Seek now has rows flowing through it.
  • Da die Buildphase des Hashjoins nicht fortgesetzt wurde, wird der zweite Branch nicht von Zeilen durchlaufen.And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

Hinweise zu adaptiven JoinsAdaptive Join remarks

Adaptive Joins erfordern mehr Speicherplatz als ein äquivalenter Plan eines indizierten Joins geschachtelter Schleifen.Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. Der zusätzliche Speicherplatz wird so angefordert, als wären die geschachtelte Schleifen ein Hashjoin.The additional memory is requested as if the Nested Loops was a Hash join. Auch die Buildphase ist mit Aufwand verbunden: sowohl für einen Stop-and-Go-Vorgang als auch für einen äquivalenten Join eines Streamings geschachtelter Schleifen.There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. Dieser zusätzliche Aufwand geht mit Flexibilität für Szenarios einher, in denen die Zeilenzahl möglicherweise in der Buildeingabe schwankt.With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

Adaptive Joins im Batchmodus funktionieren bei der ersten Ausführung einer Anweisung. Nach der ersten Kompilierung bleiben aufeinanderfolgende Ausführungen adaptiv, basierend auf dem Schwellenwert des kompilierten adaptiven Joins und den Laufzeitzeilen, die die Buildphase der äußeren Eingabe durchlaufen.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.

Wenn ein adaptiver Join zu einem Vorgang geschachtelter Schleifen wechselt, verwendet er die Zeilen, die bereits vom Hashjoinbuild gelesen wurden.If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. Der Operator liest nicht erneut die Zeilen des äußeren Verweises.The operator does not re-read the outer reference rows again.

Nachverfolgen der Aktivität adaptiver JoinsTracking Adaptive join activity

Der Operator für adaptive Joins verfügt über folgende Planoperatorattribute:The Adaptive Join operator has the following plan operator attributes:

PlanattributPlan attribute BESCHREIBUNGDescription
AdaptiveThresholdRowsAdaptiveThresholdRows Gibt den beim Wechsel von einem Hashjoin zu einem Nested Loop-Join zu verwendenden Schwellenwert anShows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType Gibt den erwarteten Jointyp anWhat the join type is likely to be.
ActualJoinTypeActualJoinType Gibt in einem Plan an, welcher Joinalgorithmus basierend auf dem Schwellenwert verwendet wurde.In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

Der geschätzte Plan zeigt die Form des adaptiven Joinplans an sowie den definierten Schwellenwert für adaptive Joins und den geschätzten Jointyp.The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.

Tipp

Der Abfragespeicher erfasst einen adaptiven Joinplan im Batchmodus und kann diesen erzwingen.Query Store captures and is able to force a batch mode Adaptive Join plan.

Zulässige Anweisungen für adaptive JoinsAdaptive join eligible statements

Ein logischer Join ist dann für adaptive Joins im Batchmodus zulässig, wenn er folgende Bedingungen erfüllt:A few conditions make a logical join eligible for a batch mode Adaptive Join:

  • Der Datenbank-Kompatibilitätsgrad ist 140 oder höher.The database compatibility level is 140 or higher.
  • Die Abfrage ist eine SELECT-Anweisung (Anweisungen zur Datenänderung sind aktuell nicht verfügbar).The query is a SELECT statement (data modification statements are currently ineligible).
  • Der Join kann sowohl vom physischen Algorithmus eines indizierten Joins geschachtelter Schleifen als auch eines Hashjoins ausgeführt werden.The join is eligible to be executed both by an indexed Nested Loops join or a Hash join physical algorithm.
  • Der Hashjoin verwendet den Batchmodus entweder über einen vorhandenen Columnstore-Index in der Abfrage oder eine mit Columnstore indizierte Tabelle, auf die direkt vom Join verwiesen wird.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.
  • Die generierten alternativen Lösungen des Joins geschachtelter Schleifen und Hashjoins sollten dasselbe erste untergeordnete Element haben (äußerer Verweis).The generated alternative solutions of the Nested Loops join and Hash join should have the same first child (outer reference).

Adaptive SchwellenwertzeilenAdaptive threshold rows

Das folgende Diagramm zeigt eine beispielhafte Überschneidung zwischen den Ressourcen eines Hashjoins und den Ressourcen des alternativen Joins geschachtelter Schleifen.The following chart shows an example intersection between the cost of a Hash join versus the cost of a Nested Loops join alternative. Am Überschneidungspunkt wird der Schwellenwert bestimmt, der wiederum den für den Joinvorgang verwendeten Algorithmus bestimmt.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

Schwellenwert des Joins

Deaktivieren von adaptiven Joins ohne Änderung des KompatibilitätsgradsDisabling Adaptive joins without changing the compatibility level

Adaptive Joins können im Datenbank- oder Anweisungsbereich deaktiviert werden, während der Datenbankkompatibilitätsgrad weiterhin bei 140 und höher bleibt.Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
Führen Sie die folgende Anweisung im Kontext der betreffenden Datenbank aus, um adaptive Joins für alle Abfrageausführungen zu deaktivieren, die aus der Datenbank stammen:To disable Adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;

Ist diese Einstellung aktiviert, wird sie in sys.database_scoped_configurations als aktiviert aufgeführt.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Um adaptive Joins für alle Abfrageausführungen wieder zu aktivieren, die aus der Datenbank stammen, führen Sie die folgende Anweisung im Kontext der betroffenen Datenbank aus:To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;

Durch Festlegen von DISABLE_BATCH_MODE_ADAPTIVE_JOINS als USE HINT-Abfragehinweis können adaptive Joins für eine bestimmte Abfrage auch deaktiviert werden.Adaptive joins can also be disabled for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. Beispiel:For example:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
       ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

Hinweis

Ein USE HINT-Abfragehinweis hat Vorrang vor einer datenbankweit gültigen Konfiguration oder einer Ablaufverfolgungsflageinstellung.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

NULL-Werte und JoinsNull Values and Joins

Wenn die Spalten der zu verknüpfenden Tabellen NULL-Werte enthalten, werden diese Werte nicht als übereinstimmend angesehen.When there are null values in the columns of the tables being joined, the null values do not match each other. Das Vorhandensein von NULL-Werten in einer Spalte aus einer der verknüpften Tabellen kann nur mithilfe eines äußeren Joins zurückgegeben werden (wenn die WHERE-Klausel keine NULL-Werte ausschließt).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).

Es folgen zwei Tabellen, bei denen NULL in der Spalte enthalten ist, die Bestandteil des Joins ist.Here are two tables that each have NULL in the column that will participate in the join:

table1                          table2
a           b                   c            d
-------     ------              -------      ------
      1        one                 NULL         two
   NULL      three                    4        four
      4      join4

Ein Join, der die Werte in der a-Spalte mit denen der c-Spalte vergleicht, erhält keine Übereinstimmung für die Zeilen, die NULL-Werte enthalten: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:

SELECT *
FROM table1 t1 JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Nur eine Zeile mit dem Wert 4 in der a-Spalte und der c-Spalte wird zurückgegeben:Only one row with 4 in column a and c is returned:

a           b      c           d      
----------- ------ ----------- ------ 
4           join4  4           four   

(1 row(s) affected)

Außerdem sind aus einer Basistabelle zurückgegebene NULL-Werte schwer von den von einem äußeren Join zurückgegebenen NULL-Werten zu unterscheiden.Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. Die folgende SELECT-Anweisung führt z. B. einen linken äußeren Join für diese beiden Tabellen aus:For example, the following SELECT statement does a left outer join on these two tables:

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
   ON t1.a = t2.c
ORDER BY t1.a;
GO

Hier ist das Resultset.Here is the result set.

a           b      c           d      
----------- ------ ----------- ------ 
NULL        three  NULL        NULL 
1           one    NULL        NULL 
4           join4  4           four   

(3 row(s) affected)

In den Ergebnissen ist ein NULL-Wert in den Daten nicht ohne Weiteres von einem NULL-Wert zu unterscheiden, der einen fehlgeschlagenen Join darstellt.The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. Wenn NULL-Werte in den zu verknüpfenden Daten enthalten sind, empfiehlt es sich, sie durch einen normalen Join aus den Ergebnissen auszuschließen.When null values are present in data being joined, it is usually preferable to omit them from the results by using a regular join.

Weitere InformationenSee Also

Referenz zu logischen und physischen Showplanoperatoren Showplan Logical and Physical Operators Reference
Vergleichsoperatoren (Transact-SQL) Comparison Operators (Transact-SQL)
Datentypkonvertierung (Datenbank-Engine) Data Type Conversion (Database Engine)
Unterabfragen Subqueries
Adaptive JoinsAdaptive Joins