側方結合の使用

FROM 句では、 LATERAL 構文により、インライン・ビューが先行するテーブル式の列をリファレンスできます。

例えば、インライン・ビューが サブクエリ の場合、サブクエリはサブクエリの左側にあるテーブルの行を処理することができます。例:

SELECT ...
  FROM left_hand_table_expression AS lhte,
    LATERAL (SELECT col_1 FROM table_2 AS t2 WHERE t2.col_1 = lhte.col_1);

この動作は、 相関サブクエリ にやや似ています。LATERAL キーワードの後のサブクエリは相関サブクエリ自体と似ており、 left_hand_table_expression はアウタークエリと似ています。ラテラル結合は、相関サブクエリとは異なり、複数の行を返すことができ、それぞれの行は複数の列を持つことができます。

他のタイプの結合では、左側のテーブル式の行を右側のテーブル式に直接渡して処理することはありません。

ラテラル結合の一般的な使用方法は、 ARRAY や VARIANT データタイプのような複雑なデータ構造を処理し、値を抽出するために、 FLATTEN テーブル関数の呼び出しと組み合わせることです。例については、 LATERAL をご参照ください。

他のタイプの結合の出力とは異なり、ラテラル結合の出力にはインラインビュー（サブクエリ）から生成された行のみが含まれます。サブクエリから行が生成された後、それらは左側のテーブルの全ての行にクロス結合することはありません。

用語

次のコードの断片について考えてみましょう。

... FROM te1, LATERAL iv1 ...

ラテラル結合の左辺はテーブル式 (te1) です。ラテラル結合の右辺はインラインビュー (iv1) です。

  • テーブル式: このトピックでは、上記の te1 というテーブル式のような、ラテラル結合の左側のテーブル式は、テーブルに対して評価される、ほとんどすべての有効な式にすることができます。例:

    • テーブル。

    • ビュー。

    • サブクエリ。

    • テーブル関数の出力。

    • 以前の結合 (ラテラル結合や他のタイプの結合) の結果。

  • インライン表示: このトピックでは、ラテラル結合 (この場合、 iv1) の右側の式を "インラインビュー "と呼びます。この文脈では、有効なインライン表示は以下のいずれかになります：

    • ステートメント内で定義された表示で、ステートメントの有効期間のみ有効。

    • サブクエリ。

    • テーブル関数: FLATTEN などの組み込みテーブル関数またはユーザー定義のテーブル関数（UDTF）のいずれか。

    インライン表示はテーブルにはできません。

  • クロス結合：このトピックでは、"クロス結合 "という用語は明示的なクロス結合だけでなく、すべてのバリエーション（自然結合、左/右/完全外側結合など）を含む内側結合と外側結合も指します。

結合についての復習

結合は2段階のプロセスです。まず、サーバーは2つの行をペアリングします。通常、これらの行は異なるテーブルにあり、ほとんどの場合、何らかの形で関連しています。次に、サーバはペアの各行の列を1つの行に結合します。

クエリ例の多くは、以下に示すデータを使用しています：

CREATE TABLE departments (department_id INTEGER, name VARCHAR);
CREATE TABLE employees (employee_ID INTEGER, last_name VARCHAR,
  department_ID INTEGER, project_names ARRAY);

INSERT INTO departments (department_ID, name) VALUES
  (1, 'Engineering'),
  (2, 'Support');
INSERT INTO employees (employee_ID, last_name, department_ID) VALUES
  (101, 'Richards', 1),
  (102, 'Paulson',  1),
  (103, 'Johnson',  2);

これは単純な内部結合です (これはラテラル結合ではありません)。

SELECT *
  FROM departments AS d, employees AS e
  WHERE e.department_ID = d.department_ID
  ORDER BY employee_ID;

+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

ご覧のように、行は、 IDs 部門の一致に基づいてペアになっています。

結合は、2つの対応する（"ペアになった"）入力行から列を取り出し、両方の入力行のすべての列を含む1つの出力行を生成します。(もちろん、 SELECT リストを修正することで、列を変更することができます。しかし、最も単純なケースでは、すべての入力列が出力に含まれます)。

ラテラル結合では行の組み合わせが異なります。しかし、プロセスの後半、ペアの行の「結合」は、類似しています。出力行には、（ほとんどの場合）入力行のペアから1つ以上の列が含まれます。

ラテラル結合による行の結合方法

ラテラル結合は他のタイプの結合とは動作が異なります。ラテラル結合は、サーバーが次のようなループを実行したかのように動作します。

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the current row in the LHT

このセクションでは、ラテラル結合とは異なる「ペアリング」の部分に焦点を当てます。

LATERAL 構築は、ラテラル結合の右側にあるインラインビューで、ビューの外側にあるテーブル式から列をリファレンスすることができます。(以下の例では、"インライン表示 "は実際にはサブクエリです)。

SELECT *
  FROM departments AS d,
    LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID) AS iv2
  ORDER BY employee_ID;

+---------------+-------------+-------------+-----------+---------------+---------------+
| DEPARTMENT_ID | NAME        | EMPLOYEE_ID | LAST_NAME | DEPARTMENT_ID | PROJECT_NAMES |
|---------------+-------------+-------------+-----------+---------------+---------------|
|             1 | Engineering |         101 | Richards  |             1 | NULL          |
|             1 | Engineering |         102 | Paulson   |             1 | NULL          |
|             2 | Support     |         103 | Johnson   |             2 | NULL          |
+---------------+-------------+-------------+-----------+---------------+---------------+

この例では、右側のサブクエリの WHERE 句は、左側のテーブルの値を参照しています。

ラテラル結合とクロス結合の違いは、単に列へのアクセスだけではありません。次の数段落では、伝統的なクロス結合から始めて、これら2つのタイプの結合を対比します。

クロス結合は、左側のテーブルの各行と右側のテーブルの各行を結合します。結果はCartesian製品です。

概念的には、クロス結合は以下の擬似コードのようにネストされたループに似ています。

for each row in left_hand_table LHT:
  for each row in right_hand_table RHT:
    concatenate the columns of the RHT to the columns of the LHT

If the table on the left has n rows and the table on the right has m rows, the result of the cross join has n x m rows. For example, if the table on the left has 1000 rows and the table on the right has 100 rows, the result of the inner join is 100,000 rows. This is just what you would expect from nested loops; if the outer loop executes 1000 times and the inner loop executes 100 times per iteration of the outer loop, the innermost statement executes 100,000 times. (Of course, SQL programmers rarely write pure cross joins without any join conditions in the FROM clause or WHERE clause.)

ラテラル結合では、レコードの組み合わせが大きく異なります。以下はラテラル結合の実装の擬似コードです。

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the LHT row,
    and concatenate LHT columns to RHS columns

ラテラル結合には、2つのネストされたループではなく、1つのループしかないため、出力が変わります。

クロス結合では、出力は100,000行でした。左側に同じ1000行のテーブルを持つラテラル結合で、入力行1つにつき1つの出力行を生成する右側のインライン表示（サブクエリなど）を使用した場合、ラテラル結合の出力は100,000行ではなく1000行になります。

ラテラル結合は以下のように考えることができます。左側のテーブルからの各入力行に対して、右側のインラインビューは0行以上の行を生成します。サブクエリからの出力行はそれぞれ、入力行（左側のテーブル全体ではあり ません）に結合され、サブクエリから選択された列と LHT 入力行からの列を含む行が生成されます。

ラテラル結合の右側のインライン表示は、必ずしも入力行ごとに1つの出力行を生成するとは限りません。任意の1つの入力行に対して、右辺からの出力は0行、1行、または複数行になります。それぞれの出力行は、元の入力行の列と結合されます。

サブクエリが各入力行に対して正確に1つの出力行を生成しない場合、ラテラル結合は必ずしも左側のテーブルと同じ数の行を生成するとは限りません。左側のテーブルに1000行があり、インラインビューが各入力行に対して2行の出力行を生成する場合、ラテラル結合の結果は2000行になります。

これまでのラテラル結合の例では、外側クエリに ON 句や WHERE 句がなく、レコードをペアにしていました。もしあれば）ペアリングは、インラインビューに渡された個々の行に基づいてインラインビューによって行われます。これは、インライン表示が WHERE 句を持つサブクエリである場合に合理的に明らかになります。右側の式がサブクエリではなくテーブル関数の場合など、他のケースでは必ずしも明らかではありません。(後の例では、サブクエリの代わりに FLATTEN テーブル関数を使用した右手式を示します)。

相関サブクエリやテーブル関数の結合に慣れている読者は、ラテラル結合がクロス結合とどのように異なるかを理解するために、以下の比較が役に立つかもしれません。相関サブクエリや結合テーブル関数に慣れていない読者は、これらのセクションを読み飛ばすことができます。

相関サブクエリとラテラル結合の類似点

ラテラル結合は相関サブクエリに似ています。

  • 相関サブクエリでは、サブクエリはアウタークエリの各行に対して1回実行されます。

  • ラテラル結合では、右側のサブクエリ（インライン表示）は左側のテーブル式の各行に対して1回実行されます。

しかし、相関サブクエリとラテラル結合は同じではありません。1つの違いは、ラテラル結合では、サブクエリは入力行1つに対して複数の出力行を生成することができ、各出力行には複数の列を含めることができることです。また、各出力行は1つの列のみを含まなければなりません。

結合テーブル関数とラテラル結合の類似点

ラテラル結合は、テーブルとユーザー定義テーブル関数 (UDTF) の間の「結合」に似ています。たとえば、次の SQL ステートメントを考えます。

SELECT *
  FROM t1, TABLE(udtf2(t1.col1))
  ...
  ;

テーブルと UDTF 間のjoinを実装する擬似コードは以下の通りです：

for each row in left_hand_table LHT:
  udtf2(row) -- that is, call udtf2() with the value(s) from the LHT row.

これは、ラテラル結合を実装するコードと基本的に同じです。

for each row in left_hand_table LHT:
  execute right_hand_subquery RHS using the values from the LHT row

例: FLATTEN テーブル関数でのラテラル結合の使用

ラテラル結合は組み込み FLATTEN テーブル関数とよく使用されます。FLATTEN 関数は、複数の値を格納できるデータ型（ARRAY、 VARIANT、 OBJECT など）でよく使用されます。例えば、配列には通常複数の値が含まれます。同様に、 VARIANT 列は、 JSON データ値を含むことができます。 データ値は、ディクショナリ（ハッシュ）またはリストを含む可能性があります。(そして、そのコンテナーには他の値が含まれているかもしれません）。

ARRAY の値は以下のように作成できます：

UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'UDFs')
  WHERE employee_ID = 101;
UPDATE employees SET project_names = ARRAY_CONSTRUCT('Materialized Views', 'Lateral Joins')
  WHERE employee_ID = 102;

FLATTEN 関数は、それらの値の内部から値を抽出することができます。この関数は、 VARIANT、 OBJECT、または ARRAY のタイプの式を1つ取り、その式から値を行のセット（0行以上、各行は1列以上を含む）に抽出します。この行のセットは、表示やテーブルに相当します。この表示は、それが定義されているステートメントの間だけ存在するので、一般に「インライン・ビュー」と呼ばれます。

以下の例では、 FLATTEN を使用して配列 (*) から値を抽出しています (ラテラル結合は使用していません*)。

SELECT index, value AS project_name
  FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('project1', 'project2')));

+-------+--------------+
| INDEX | PROJECT_NAME |
|-------+--------------|
|     0 | "project1"   |
|     1 | "project2"   |
+-------+--------------+

FLATTEN によって生成されたインライン表示は、 LATERAL キーワードとともに使用することができます（ただし、使用する必要はありません）。例:

SELECT * FROM table1, LATERAL FLATTEN(...);

LATERAL キーワードと一緒に使用すると、インライン・ビューはその前にあるテーブルの列へのリファレンスを含むことができます：

SELECT emp.employee_ID, emp.last_name, index, value AS project_name
  FROM employees AS emp,
    LATERAL FLATTEN(INPUT => emp.project_names) AS proj_names
  ORDER BY employee_ID;

+-------------+-----------+-------+----------------------+
| EMPLOYEE_ID | LAST_NAME | INDEX | PROJECT_NAME         |
|-------------+-----------+-------+----------------------|
|         101 | Richards  |     0 | "Materialized Views" |
|         101 | Richards  |     1 | "UDFs"               |
|         102 | Paulson   |     0 | "Materialized Views" |
|         102 | Paulson   |     1 | "Lateral Joins"      |
+-------------+-----------+-------+----------------------+