ジョイン（Join）とは、2つ以上のテーブル、ビュー、またはマテリアライズドビューを結合したクエリのことです。クエリのFROM句に複数のテーブルが指定されている場合、OceanBaseデータベースはジョインクエリを実行し、クエリの出力列はFROM句に記述された任意のテーブルから選択できます。複数のテーブルに同じ列名が存在する場合、これらの列へのすべての参照をテーブル名で限定する必要があります。
接合タイプ
データベースにおける接合タイプには、内部結合、外部結合、およびセミ結合（SEMI JOIN / ANTI JOIN）の3種類があります。そのうちセミ結合はサブクエリによって書き換えられるものであり、OceanBaseデータベース自体には対応する構文が用意されていません。
接合条件
接合条件とは、複数のテーブルを結合するためのルールであり、ほとんどの接合には少なくとも1つの接合条件が含まれます。これらの条件はFROM句またはWHERE句に存在し、異なるテーブルからの2つの列を比較するために使用されます。WHERE句には接合条件以外に他の条件も含まれる場合があり、これらの条件は単一のテーブルを参照するだけであり、接合クエリが返す行数をさらに制限することができます。
接合条件は、等価結合（例：t1.a = t2.b）と不等号結合（例：t1.a < t2.b）に分類されます。不等号結合条件と比較して、等価結合条件ではデータベースが効率的な接合アルゴリズム、例えばMERGE JOIN（MJ）やHASH JOIN（HJ）を使用できます。
接合を実行するために、OceanBaseデータベースは異なるテーブルから行を抽出し、ペアとして組み合わせて接合条件を用いてマッチングします。2つ以上のテーブルの多表接合を実行するには、OceanBaseデータベースはまずそれらの列の接合条件に基づいてそのうちの2つのテーブルを結合し、次に結合されたテーブルと新しいテーブルの列を含む接合条件に基づいて結果を別のテーブルに結合します。オプティマイザーは、接合条件、ベーステーブルのインデックス、および利用可能な統計情報に基づいて、OceanBaseデータベースの接合順序を決定します。
説明
WHERE句に接合条件が含まれている場合、WHERE句でLOB列を指定することはできません。
接続方式
OceanBaseデータベースの現行バージョンがサポートする接続方式は、以下の表に示されています。
| 接続タイプ | 英文表記 | 説明 |
|---|---|---|
| 等価結合 | EQUI JOIN | 等式演算子を用いた結合条件を含む結合。 |
| セルフ結合 | SELF JOIN | テーブルとその自身との結合。 |
| 内部結合 | INNER JOIN | 内部結合は、結合された2つのテーブルのマッチする行のみを結合した結果を返します。 |
| 左（外部）結合 | LEFT [OUTER] JOIN | 結果には、JOIN 句の左側に記述されている左側のテーブルのすべての行が含まれますが、右側のテーブルのマッチしない行は含まれません。 |
| 右（外部）結合 | RIGHT [OUTER] JOIN | 結果には、JOIN 句の右側に記述されている右側のテーブルのすべての行が含まれますが、左側のテーブルのマッチしない行は含まれません。 |
| 完全（外部）結合 | FULL [OUTER] JOIN | 結果には、マッチするかどうかに関係なく、すべての結合されたテーブルのすべての行が含まれます。 |
| SEMI結合 | SEMI JOIN | サブクエリによってのみ展開できます。 |
| ANTI結合 | ANTI JOIN | 右側のテーブルとマッチしない左側のテーブルのすべてのレコードを返します。 |
| デカルト積 | Cartesian Product | 2つのテーブルに結合操作がない場合、これら2つのテーブルに対してクエリを実行して得られるデータは、これら2つのテーブルのデカルト積です。 |
等価結合
等価結合とは、等価演算子を含む結合のことです。等価結合を実行する際には、指定された列が等価条件を満たす行を組み合わせて出力します。
セルフ結合
セルフ結合とは、テーブルとその自身との結合のことです。このテーブルは FROM 句で2回記述され、その後にテーブルエイリアスが続きます。これらのエイリアスは結合条件の列名を限定します。セルフ結合を実行すると、OceanBaseデータベースは結合条件を満たす行を組み合わせて返します。
デカルト積
結合クエリ内の2つのテーブルに結合条件がない場合、OceanBaseデータベースはそれらのデカルト積を返し、最初のテーブルの各行をもう一方のテーブルの各行と組み合わせて出力します。デカルト積は多くの行を生成しますが、そのほとんどは無用なデータです。例えば、両方とも100行のテーブルのデカルト積は10,000行になるため、特にデカルト積が必要でない限り、OceanBaseデータベースではクエリに常に結合条件を含めることを推奨しており、デカルト積を返さないようにしています。
クエリが3つ以上のテーブルを結合し、結合条件が指定されていない場合、オプティマイザーは中間でデカルト積が生成されるのを避けるために、ある結合順序を選択できます。
内部結合
内部結合は、データベースにおける最も基本的な結合操作です。
内部結合は結合条件に基づいて、2つのテーブル（例えばAとB）の列を組み合わせ、新しい結果をテーブルに格納します。クエリはAテーブルの各行とBテーブルの各行を比較し、結合条件を満たす組み合わせを見つけ出します。結合条件を満たす場合、テーブルAとBのマッチする行は列ごとに組み合わせ（並べて組み合わせ）され、結果セットの1行となります。結合によって生成される結果セットは、まず2つのテーブルに対してデカルト積を行い、テーブルAの各行とテーブルBの各行を組み合わせた上で、結合条件を満たすレコードを返すことに等しいです。
外部結合
外部結合は、結合条件を満たすすべての行を返します。同時に、使用されない行は一方のテーブルから返され、もう一方のテーブルの対応する位置には NULL が埋め込まれます。
外部結合は、結合されたテーブルに基づいて、左側のテーブル、右側のテーブル、または両方のテーブルの行を保持するかどうかによって、左結合、右結合、完全結合にさらに分類されます。その中で、左結合（LEFT [OUTER] JOIN）では、左側のテーブルの行が右側のテーブルでマッチする行が見つからない場合、右側のテーブルに自動的に NULL が埋め込まれます。右結合（RIGHT [OUTER] JOIN）では、右側のテーブルの行が左側のテーブルでマッチする行が見つからない場合、左側のテーブルに自動的に NULL が埋め込まれます。完全結合（FULL [OUTER] JOIN）では、左側または右側のテーブルが他のテーブルでマッチする行が見つからない場合には、どちらにも NULL が埋め込まれます。
OceanBaseデータベースは外部結合記号（+）もサポートしています。左結合の場合、WHERE 句の結合条件において、右側のテーブルのすべての列に外部結合記号（+）を適用します。右結合の場合、WHERE 句の結合条件において、左側のテーブルのすべての列に外部結合記号（+）を適用します。外部結合記号（+）を使用する際には、以下のルールと制限が適用されます：
クエリブロックに
FROM句JOIN構文が含まれている場合、外部結合記号（+）を指定することはできません。外部結合記号（+）は
WHERE句内、またはFROM句の左関連コンテキスト（TABLE句を指定した場合）にのみ現れることができ、しかもテーブルまたはビューの1列にのみ適用できます。左側のテーブルと右側のテーブルが複数の結合条件で結合されている場合、すべての条件で外部結合記号（+）を使用する必要があります。そうでない場合、データベースは単純結合によって生成された行データのみを返し、警告やエラーは表示されません。
外部結合記号（+）を使用して、テーブルを自身に外部結合することはできません。
外部結合演算子（+）は列にのみ適用でき、式には適用できません。
外部結合記号（+）を含む
WHERE条件は、ORおよびIN論理演算子を使用した条件と組み合わせることはできません。2対以上のテーブルに対して外部結合を実行するクエリでは、そのうちの1つのテーブルは、もう一方のテーブルのためだけに生成された空のテーブルである可能性があります。したがって、AとBの結合条件およびBとCの結合条件において、外部結合記号（+）をBの列に適用することはできません。つまり、複数の結合条件における同一の列に外部結合演算子（+）を適用することはできません。
SEMI結合
AテーブルとBテーブルで LEFT SEMI JOIN または RIGHT SEMI JOIN を実行する場合、BまたはAでマッチする行が見つかるAまたはBのすべての行のみが返されます。SEMI JOIN はサブクエリを展開することによってのみ得られます。
ANTI結合
AテーブルとBテーブルで LEFT ANTI JOIN または RIGHT ANTI JOIN を実行する場合、BまたはAでマッチする行が見つからないAまたはBのすべての行のみが返されます。SEMI JOIN と同様に、ANTI JOIN もサブクエリを展開することによってのみ得られます。
例
テーブル table_a とテーブル table_b を作成し、データを挿入します。
obclient> CREATE TABLE table_a(PK INT, name VARCHAR(25));
obclient> INSERT INTO table_a VALUES(1,'Fox');
obclient> INSERT INTO table_a VALUES(2,'Police');
obclient> INSERT INTO table_a VALUES(3,'Taxi');
obclient> INSERT INTO table_a VALUES(4,'Lincoln');
obclient> INSERT INTO table_a VALUES(5,'Arizona');
obclient> INSERT INTO table_a VALUES(6,'Washington');
obclient> INSERT INTO table_a VALUES(7,'Dell');
obclient> INSERT INTO table_a VALUES(10,'Lucent');
obclient> CREATE TABLE table_b(PK INT, name VARCHAR(25));
obclient> INSERT INTO table_b VALUES(1,'Fox');
obclient> INSERT INTO table_b VALUES(2,'Police');
obclient> INSERT INTO table_b VALUES(3,'Taxi');
obclient> INSERT INTO table_b VALUES(6,'Washington');
obclient> INSERT INTO table_b VALUES(7,'Dell');
obclient> INSERT INTO table_b VALUES(8,'Microsoft');
obclient> INSERT INTO table_b VALUES(9,'Apple');
obclient> INSERT INTO table_b VALUES(11,'Scottish Whisky');
自己結合クエリの例
obclient> SELECT * FROM table_a ta, table_a tb WHERE ta.NAME = tb.NAME;
期待される実行結果：
+------+------------+------+------------+
| PK | NAME | PK | NAME |
+------+------------+------+------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 4 | Lincoln | 4 | Lincoln |
| 5 | Arizona | 5 | Arizona |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
| 10 | Lucent | 10 | Lucent |
+------+------------+------+------------+
8 rows in set (0.013 sec)
内部結合クエリの例
obclient> SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A INNER JOIN table_b B ON A.PK = B.PK;
期待される実行結果：
+------+------------+------+------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+------------+------+------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
+------+------------+------+------------+
5 rows in set (0.017 sec)
左結合クエリの例
obclient> SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A LEFT JOIN table_b B ON A.PK = B.PK;
期待される実行結果：
+------+------------+------+------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+------------+------+------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
| 4 | Lincoln | NULL | NULL |
| 5 | Arizona | NULL | NULL |
| 10 | Lucent | NULL | NULL |
+------+------------+------+------------+
8 rows in set (0.006 sec)
obclient> SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A,table_b B WHERE A.PK = B.PK(+);
期待される実行結果：
+------+------------+------+------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+------------+------+------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
| 4 | Lincoln | NULL | NULL |
| 5 | Arizona | NULL | NULL |
| 10 | Lucent | NULL | NULL |
+------+------------+------+------------+
8 rows in set (0.008 sec)
右結合クエリの例
obclient> SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A RIGHT JOIN table_b B ON A.PK = B.PK;
期待される実行結果：
+------+------------+------+-----------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+------------+------+-----------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
| NULL | NULL | 11 | Scottish Whisky |
| NULL | NULL | 9 | Apple |
| NULL | NULL | 8 | Microsoft |
+------+------------+------+-----------------+
8 rows in set (0.005 sec)
obclient> SELECT A.PK AS A_PK, A.name AS A_Value, B.PK AS B_PK, B.name AS B_Value
FROM table_a A,table_b B WHERE A.PK(+) = B.PK;
期待される実行結果：
+------+------------+------+-----------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+------------+------+-----------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
| NULL | NULL | 11 | Scottish Whisky |
| NULL | NULL | 9 | Apple |
| NULL | NULL | 8 | Microsoft |
+------+------------+------+-----------------+
8 rows in set (0.005 sec)
全結合クエリの例
obclient> SELECT A.PK AS A_PK,A.name AS A_Value,B.PK AS B_PK,B.name AS B_Value
FROM table_a A FULL JOIN table_b B ON A.PK = B.PK;
期待される実行結果：
+------+------------+------+-----------------+
| A_PK | A_VALUE | B_PK | B_VALUE |
+------+------------+------+-----------------+
| 1 | Fox | 1 | Fox |
| 2 | Police | 2 | Police |
| 3 | Taxi | 3 | Taxi |
| 6 | Washington | 6 | Washington |
| 7 | Dell | 7 | Dell |
| NULL | NULL | 8 | Microsoft |
| NULL | NULL | 9 | Apple |
| NULL | NULL | 11 | Scottish Whisky |
| 4 | Lincoln | NULL | NULL |
| 5 | Arizona | NULL | NULL |
| 10 | Lucent | NULL | NULL |
+------+------------+------+-----------------+
11 rows in set (0.005 sec)
SEMI結合クエリの例
依存関係を持つサブクエリを展開して、SEMI JOINに書き換えます。
obclient> EXPLAIN SELECT * FROM table_a t1 WHERE t1.PK IN (SELECT t2.PK FROM table_b t2
WHERE t2.NAME = t1.NAME);
期待される実行結果：
+---------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH SEMI JOIN | |8 |11 | |
| |1 |├─TABLE FULL SCAN|T1 |8 |3 | |
| |2 |└─TABLE FULL SCAN|T2 |8 |3 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.PK], [T1.NAME]), filter(nil), rowset=16 |
| equal_conds([T2.NAME = T1.NAME], [T1.PK = T2.PK]), other_conds(nil) |
| 1 - output([T1.NAME], [T1.PK]), filter(nil), rowset=16 |
| access([T1.NAME], [T1.PK]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([T2.NAME], [T2.PK]), filter(nil), rowset=16 |
| access([T2.NAME], [T2.PK]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T2.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------+
19 rows in set (0.006 sec)
ANTI結合クエリの例
依存関係を持つサブクエリは、ANTI JOINに書き換えられます。
obclient> EXPLAIN SELECT * FROM table_a t1 WHERE t1.PK NOT IN (SELECT t2.PK
FROM table_b t2 WHERE t2.name = t1.name);
期待される実行結果：
+---------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |HASH ANTI JOIN | |1 |11 | |
| |1 |├─TABLE FULL SCAN|T1 |8 |3 | |
| |2 |└─TABLE FULL SCAN|T2 |8 |3 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.PK], [T1.NAME]), filter(nil), rowset=16 |
| equal_conds([T2.NAME = T1.NAME]), other_conds([(T_OP_OR, T1.PK = T2.PK, T1.PK IS NULL, T2.PK IS NULL)]) |
| 1 - output([T1.NAME], [T1.PK]), filter(nil), rowset=16 |
| access([T1.NAME], [T1.PK]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([T2.NAME], [T2.PK]), filter(nil), rowset=16 |
| access([T2.NAME], [T2.PK]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T2.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------------------------------------------+
19 rows in set (0.008 sec)