結合（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)