結合（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行ある2つのテーブルのデカルト積は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つのテーブルは、もう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)