結合 (SQL Server)Joins (SQL Server)

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

SQL ServerSQL Server では、インメモリの並べ替えとハッシュ結合テクノロジを使用して、並べ替え、積集合、和集合、および差集合の各演算が実行されます。performs sort, intersect, union, and difference operations using in-memory sorting and hash join technology. この種のクエリ プランを使用すると、SQL ServerSQL Server によってテーブルの列分割がサポートされます。Using this type of query plan, SQL ServerSQL Server supports vertical table partitioning.

SQL ServerSQL Server では、Transact-SQLTransact-SQL 構文によって決定される論理結合操作が実装されます。implements logical join operations, as determined by Transact-SQLTransact-SQL syntax:

  • 内部結合Inner join
  • 左外部結合Left outer join
  • 右外部結合Right outer join
  • 完全外部結合Full outer join
  • クロス結合Cross join

注意

結合構文の詳細については、「FROM 句と JOIN、APPLY、PIVOT (Transact-SQL)」を参照してください。For more information on join syntax, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL).

SQL ServerSQL Server には、論理結合操作を実行するために、次の 4 種類の物理結合操作が採用されています。employs four types of physical join operations to carry out the logical join operations:

  • ネステッド ループ結合Nested Loops joins
  • マージ結合Merge joins
  • ハッシュ結合Hash joins
  • アダプティブ結合 (SQL Server 2017 (14.x)SQL Server 2017 (14.x) 以降)Adaptive joins (starting with SQL Server 2017 (14.x)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. 結合では、SQL ServerSQL Server が 1 つのテーブルからのデータを使用して別のテーブル内の行を選択する方法を指定します。Joins indicate how SQL ServerSQL Server should use data from one table to select the rows in another table.

結合条件は、クエリ内の 2 つのテーブルの関係を定義します。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. 一般的な結合条件では、1 つのテーブルから外部キーを指定し、他のテーブルでそれに対応したキーを指定します。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.

結合は、次の Transact-SQLTransact-SQL 構文を使用して論理的に表現されます。Joins are expressed logically using the following Transact-SQLTransact-SQL syntax:

  • INNER JOININNER JOIN
  • LEFT [ OUTER ] JOINLEFT [ OUTER ] JOIN
  • RIGHT [ OUTER ] JOINRIGHT [ OUTER ] JOIN
  • FULL [ OUTER ] JOINFULL [ OUTER ] JOIN
  • CROSS JOINCROSS JOIN

内部結合 は、FROM 句または WHERE 句のどちらを使用しても指定できます。Inner joins can be specified in either the FROM or WHERE clauses. 外部結合クロス結合 は、FROM 句でのみ指定できます。Outer joins and cross joins can be specified in the FROM clause only. 結合条件は、検索条件 WHEREHAVING を使用して、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.

FROM 句で結合条件を指定すると、WHERE 句で他の検索条件が指定された場合に区別しやすくなります。結合を指定する場合、FROM 句で指定することをお勧めします。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. 簡略化された ISO FROM 句の結合構文は次のとおりです。A simplified ISO FROM clause join syntax is:

FROM first_table < join_type > second_table [ ON ( join_condition ) ]

join_type は、実行する結合の種類 (内部、外部、クロス) を指定します。join_type specifies what kind of join is performed: an inner, outer, or cross join. join_condition は、結合された各行を評価するための述語を定義します。join_condition defines the predicate to be evaluated for each pair of joined rows. 以下に FROM 句での結合指定の例を示します。The following is an example of a FROM clause join specification:

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

次の例は、この結合を使用したシンプルな SELECT ステートメントを示します。The following is a simple SELECT statement using this join:

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

この SELECT ステートメントからは、企業名が F で始まる企業が提供する製品のうち価格が 10 ドルを超えている部品について、製品と納入業者の情報が返されます。The SELECT statement 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.

1 つのクエリで複数のテーブルを参照する場合、どの列参照も明確である必要があります。When multiple tables are referenced in a single query, all column references must be unambiguous. 上記の例では、ProductVendor および Vendor テーブルの両方に、BusinessEntityID という名前の列があります。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. この例では Vendor 列への参照はすべて修飾されています。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. このような SELECT 句は、どのテーブルの列か示されていないので、わかりにくいことがあります。Such a SELECT clause 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:

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

上記の例では、FROM 句 (推奨の方法) で結合条件を指定していました。The previous examples specified the join conditions in the FROM clause, which is the preferred method. 次の例に示すように、これと同じ結合条件を持つクエリを WHERE 句でも指定できます。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%';

結合の SELECT リストは、結合されたテーブル内のすべての列を参照することも、一部の列だけを参照することもできます。The SELECT list for a join can reference all the columns in the joined tables, or any subset of the columns. SELECT リストには、結合したすべてのテーブルの列を含める必要はありません。The SELECT list is not required to contain columns from every table in the join. たとえば、3 つのテーブルを結合した場合、1 つのテーブルだけを使用して残りの 2 つのテーブルの一方から 3 番目のテーブルにブリッジできます。中央のテーブルの列を選択リストで参照する必要はありません。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. これは、反準結合 とも呼ばれます。This is also called an anti semi join.

結合条件では、通常は等値比較演算子 (=) を使用します。ただし、他の述語と同様に他の比較演算子や関係演算子も指定できます。Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates. 詳しくは、「比較演算子 (Transact-SQL)」および「WHERE (Transact-SQL)」をご覧ください。For more information, see Comparison Operators (Transact-SQL) and WHERE (Transact-SQL).

つまり、SQL ServerSQL Server で結合を処理する場合、クエリ オプティマイザーによって、複数の候補の中から最も効率的な結合の処理方法が選択されます。When SQL ServerSQL Server processes joins, the Query Optimizer chooses the most efficient method (out of several possibilities) of processing the join. これには、最も効率的な物理結合の種類、テーブルが結合される順序をそれぞれ選択することや、Transact-SQLTransact-SQL 構文で直接表現できない論理結合操作の種類 (準結合反準結合 など) を使用することも含まれます。This includes choosing the most efficient type of physical join, the order in which the tables will be joined, and even using types of logical join operations that cannot be directly expressed with Transact-SQLTransact-SQL syntax, such as semi joins and anti semi joins. 各結合の物理的実行には何種類もの最適化を使用できるので、その実行を確実に予測することはできません。The physical execution of various joins can use many different optimizations and therefore cannot be reliably predicted. 準結合と反準結合の詳細については、「プラン表示の論理操作と物理操作のリファレンス」を参照してください。For more information on semi joins and anti semi joins, see Showplan Logical and Physical Operators Reference.

結合条件で使用する複数の列間で名前やデータ型が同じである必要はありません。Columns used in a join condition are not required to have the same name or be the same data type. ただし、データ型が同じでない場合、それらは互換性があるもの、または SQL ServerSQL Server が暗黙的に変換できるものである必要があります。However, if the data types are not identical, they must be compatible, or be types that SQL ServerSQL Server can implicitly convert. データ型を暗黙的に変換できない場合、結合条件は CAST 関数を使用してデータ型を明示的に変換する必要があります。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.

注意

ntext 列、text 列、または image 列を使用してテーブルを直接結合することはできません。Tables cannot be joined directly on ntext, text, or image columns. ただし、SUBSTRING を使用すれば、ntext 列、text 列、または image 列でテーブルを間接的に結合できます。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) は t1 テーブルと t2 テーブルの各テキスト列の先頭の 20 文字について 2 つのテーブル内部結合を実行します。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.
WHERE 句を使用して列の長さを比較して、2 つのテーブルの ntext 列または text 列を比較することもできます。例: 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)

入れ子になっているループ結合についてUnderstanding Nested Loops joins

一方の結合入力が少なく (たとえば 10 行未満)、他方の結合入力が多く、その結合列にインデックスが設定されている場合、インデックスの入れ子になったループが最も高速な結合演算です。入れ子になったループは I/O が最も少なく、比較が最も少なくなるためです。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. 外部ループでは、外部入力テーブルを 1 行ずつ使用します。The outer loop consumes the outer input table row by row. 内部ループは、外部行の 1 行ごとに実行され、内部入力テーブルで一致行を検索します。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.

ネステッド ループ結合演算子の OPTIMIZED 属性が True に設定されている場合は、内部テーブルが大きいときに、それが並列化されているかどうかに関係なく、最適化されたネステッド ループ (またはバッチ並べ替え) を使用して I/O を最小化することを意味します。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. しかし、OPTIMIZED 属性の XML プランを見ると、ネステッド ループ結合では 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.

マージ結合についてUnderstanding Merge joins

2 つの結合入力が少なくない場合でも、結合列に基づいて並べ替えられている場合 (並べ替えられたインデックスのスキャンにより取得された場合など)、マージ結合が最も高速な結合演算です。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. 両方の結合入力が多く、2 つの入力が同じようなサイズの場合、あらかじめ並べ替えられたマージ結合とハッシュ結合は同じようなパフォーマンスになります。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. ただし、2 つの入力サイズが大きく異なる場合、ハッシュ結合演算の方がはるかに高速になることが多くなります。However, hash join operations are often much faster if the two input sizes differ significantly from each other.

マージ結合では、両方の入力がマージ列を基準に並べ替えられていることが必要です。マージ列は、結合述語の等値 (ON) 句によって定義されます。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. 通常、インデックスが対応する 1 組の列に対して存在する場合、クエリ オプティマイザーはインデックスをスキャンします。または、マージ結合の下に並べ替え操作が配置されます。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.

各入力が並べ替えられるので、Merge Join 操作は各入力から 1 行ずつ取得して、それらを比較します。Because each input is sorted, the Merge Join operator gets a row from each input and compares them. たとえば、Inner Join 操作では、行が等しい場合、それらの行が返されます。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.

Merge Join 操作は、標準の操作または多対多操作のいずれかになります。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. ただし、データ量が多く、必要なデータを既存の B ツリー インデックスからあらかじめ並べ替えられた形で取得できる場合、多くの場合、利用可能な結合アルゴリズムの中でマージ結合が最も高速になります。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.

ハッシュ結合には、ビルド 入力と プローブ 入力という 2 つの入力があります。The hash join has two inputs: the build input and probe input. クエリ オプティマイザーでは、2 つの入力のうち小さい方がビルド入力になるように、ロールを割り当てます。The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

ハッシュ結合は多種多様な集合の照合操作に使用されます。ハッシュ結合を使用できるのは、Inner Join、Left Outer Join、Right Outer Join、Full Outer Join、Left Semi Join、Right Semi Join、Intersect、Union、Diff です。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. また、ハッシュ結合の派生形では、重複の削除やグループ化を行うことができます (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. このような変更では、ビルドとプローブの両方のロールに 1 つの入力しか使用しません。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. プローブ入力全体が 1 回に 1 行ずつスキャンまたは計算されます。プローブ行ごとにハッシュ キーの値が計算され、対応するハッシュ バケットがスキャンされて、照合が行われます。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. ハッシュ キーにハッシュ関数を使用すると、2 つの結合レコードが同じファイルの組に含まれることが保証されます。Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. したがって、2 つの大規模な入力を結合する作業は、小規模な同一作業の複数のインスタンスになります。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. すべてのパーティション分割ステップをできるだけ高速にするには、大規模な非同期 I/O 操作を使用し、1 つのスレッドで複数のディスク ドライブを集中的に使用できるようにします。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.

注意

ビルド入力がわずかに使用可能なメモリを超える場合は、インメモリ ハッシュ結合と猶予ハッシュ結合の要素が 1 つのステップに組み合わされ、ハイブリッド ハッシュ結合が作成されます。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. このため SQL Server では、始めにインメモリ ハッシュ結合が使用され、ビルド入力のサイズに従って、徐々に猶予ハッシュ結合と再帰的ハッシュ結合に移行します。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.

クエリ オプティマイザーが、2 つの入力のうち、ビルド入力になるべき小さい方を誤って予想した場合、ビルド ロールとプローブ ロールは動的に逆転されます。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. ロール逆転は、ディスクへの書き込みが少なくとも 1 回行われた後にハッシュ結合の内部で行われます。Role reversal occurs inside the hash join after at least one spill to the disk.

注意

ロール逆転は、クエリ ヒントやクエリの構造とは無関係に行われます。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.

注意

再帰的ハッシュ結合またはハッシュの保留により、サーバーのパフォーマンスが低下します。Recursive hash joins or hash bailouts cause reduced performance in your server. トレース内に多数の Hash Warning イベントを確認した場合は、結合対象の列の統計を更新します。If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.

ハッシュの保留について詳しくは、「Hash Warning イベント クラス」をご覧ください。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.

ヒント

大小の結合入力スキャンが頻繁に切り替わるワークロードの場合、この機能から最もメリットがあります。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:

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;

このクエリは 336 行を返します。The query returns 336 rows. ライブ クエリ統計を有効にすると次のようなプランが表示されます。Enabling Live Query Statistics displays the following plan:

クエリ結果 336 行

このプランでは、次のことがわかります。In the plan, note the following:

  1. ハッシュ結合ビルド フェーズ用の行を提供するために、列ストア インデックス スキャンが使われています。A columnstore index scan used to provide rows for the Hash join build phase.
  2. 新しいアダプティブ結合演算子があります。The new Adaptive Join operator. この演算子は、入れ子になったループ プランに切り替えるタイミングを決定するために使われるしきい値を定義します。This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. この例では、しきい値は 78 行です。For this example, the threshold is 78 rows. 行数が >= 78 の場合はいずれでも、ハッシュ結合が使われます。Anything with >= 78 rows will use a Hash join. しきい値より小さい場合は、ネステッド ループ結合が使用されます。If less than the threshold, a Nested Loops join will be used.
  3. クエリからは 336 行が返されるため、これはしきい値を超えており、2 番目の分岐は標準的なハッシュ結合操作のプローブ フェーズを表します。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. ライブ クエリ統計は演算子を通過する行数を示すことに注意してください (この場合は "672 of 672")。Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
  4. そして、最後の分岐は、しきい値を超えていないネステッド ループ結合で使用するためのクラスター化インデックス シークです。And the last branch is a Clustered Index Seek for use by the Nested Loops join had the threshold not been exceeded. "0 of 336" 行と表示されます (分岐は使われません)。Notice that we see "0 of 336" rows displayed (the branch is unused).

次は、同じクエリでプランを比較します。ただし、この場合、Quantity 値にはテーブル内の行が 1 つ含まれるだけです。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;

クエリでは 1 行が返されます。The query returns one row. ライブ クエリ統計を有効にすると次のようなプランが表示されます。Enabling Live Query Statistics displays the following plan:

クエリ結果 1 行

このプランでは、次のことがわかります。In the plan, note the following:

  • 返されるのが 1 行なので、クラスター化インデックス シークを行が通過します。With one row returned, the Clustered Index Seek now has rows flowing through it.
  • また、ハッシュ結合ビルド フェーズは続行しなかったので、2 番目の分岐を通る行はありません。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
AdaptiveThresholdRowsAdaptiveThresholdRows ハッシュ結合から入れ子になったループ結合への切り替えに使われるしきい値を示します。Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType 予想される結合の種類を示します。What the join type is likely to be.
ActualJoinTypeActualJoinType 実際のプランで、しきい値に基づいて最終的に選ばれた結合アルゴリズムを示します。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.

ヒント

クエリ ストアは、バッチ モード アダプティブ結合プランをキャプチャし、強制できます。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:

  • データベース互換性レベルが 140 以上である。The database compatibility level is 140 or higher.
  • クエリが SELECT ステートメントである (現在、データ変更ステートメントは使えません)。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, enabled through the presence of a Columnstore index in the query overall, a Columnstore indexed table being referenced directly by the join, or through the use of the Batch mode on rowstore.
  • ネステッド ループ結合とハッシュ結合の生成された代替ソリューションが、同じ最初の子 (外部参照) を持っている。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

適応型結合は、データベースの互換性レベル 140 以上を維持しながら、データベースまたはステートメント範囲で無効にできます。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:

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

有効になっているとき、この設定は sys.database_scoped_configurations で有効として表示されます。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:

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

DISABLE_BATCH_MODE_ADAPTIVE_JOINSUSE HINT クエリ ヒントとして を指定することで、特定のクエリでアダプティブ結合を無効にすることもできます。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:

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

注意

USE HINT クエリ ヒントは、データベース スコープ構成またはトレース フラグ設定に優先します。A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

NULL 値と結合Null Values and Joins

結合されるテーブルの列に NULL 値がある場合、双方の NULL 値が互いに一致することはありません。When there are null values in the columns of the tables being joined, the null values do not match each other. 結合されるいずれかのテーブルの列に複数の NULL 値がある場合にそれを返すのは、外部結合を使用した場合だけです。ただし WHERE 句によって 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).

次に 2 つのテーブルを示しています。どちらのテーブルでも、結合に使われる列に NULL 値が含まれています。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

列 a の値と列 c の値を比較する結合の場合、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:

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

列 a と列 c で返されるのは、値が 4 の行 1 つだけです。Only one row with 4 in column a and c is returned:

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

(1 row(s) affected)

ベース テーブルから返される NULL 値と外部結合から返される NULL 値を区別することは困難です。Null values returned from a base table are also difficult to distinguish from the null values returned from an outer join. たとえば、次の SELECT ステートメントは、2 つのテーブルの左外部結合を行います。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

結果セットは次のようになります。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)

この結果では、データ内の NULL を、結合の失敗を表す NULL と区別するのが簡単ではありません。The results do not make it easy to distinguish a NULL in the data from a NULL that represents a failure to join. 結合されるデータ内に NULL 値がある場合、通常は、普通の結合を使ってこれらの NULL 値を結果から除外することをお勧めします。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
比較演算子 (Transact-SQL) Comparison Operators (Transact-SQL)
データ型の変換 (データベース エンジン) Data Type Conversion (Database Engine)
サブクエリ Subqueries
適応型結合 Adaptive Joins
FROM 句と JOIN、APPLY、PIVOT (Transact-SQL)FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)