SQL文におけるJOINの利用シーンの分析

レコード：256

最高レベルのSQLの書き方です。SELECT * FROM テーブル名. もちろんこれは自虐的なフレーズです。このシナリオを経て、まさにそれを検証したくなるまで、SQL文のJOINの使い方を探ってみてください。

I. シナリオ

リレーショナルデータベースAからビッグデータプラットフォームM（MaxCompute Big Data Platform）にテーブルTEST_TB01とTEST_TB02を移行します。TEST_TB01は1つのテーブルに1000万レコード、TEST_TB02は1つのテーブルに80万レコードが登録されている。

リレーショナルデータベースにおいて、TEST_TB01とTEST_TB02は主キー制約を持ちます。新しいビジネスデータが生成されたとき、重複してデータが挿入されることはありません。しかし、ビッグデータ基盤にデータを移行する場合、ビッグデータ基盤には主キー制約がない。新しいビジネスデータが生成されると、TEST_TB01とTEST_TB02の両方に重複したデータが挿入されます。

計算タスクにおいて、TEST_TB01とTEST_TB02がフィールドJOIN結合に基づいて結果データを計算し、そのデータをユーザーのリレーショナルデータベースCにプッシュしました。これは直接、データベースCの該当テーブルの表領域をバーストさせ、警告を監視しました。

原因は TEST_TB01とTEST_TB02はデータが重複しており、JOIN結合を使用した後、10億以上のデータ、合計200G以上のデータが生成され、Cデータベースへ直接プッシュされました。

それが下手すると一瞬で混乱し、SQL文のJOINがよくわからなくなった感じです。だから、記録のために探っておきたかったんです。

II. テーブルの構築

TEST_TB01テーブルのビルド文です。

create table TEST_TB01
(
  sensor_id BIGINT,
  part_id BIGINT
 )
COMMENT 'Data table one';

TEST_TB02テーブル構築文。

create table TEST_TB02
(
  part_id BIGINT,
  elem_id BIGINT
 )
 COMMENT 'data table two';

第三に、重複データ事例がないSQL文でのJOINの使用について

SQL文中のJOINは、重複データケースがないように、つまり、TEST_TB01とTEST_TB02の両方のテーブルで重複データケースがないように使用します。それぞれJOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOINを使って検証してください。

TEST_TB01にデータを挿入します。

insert into TEST_TB01 (sensor_id,part_id) values(2101,9911);
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);
insert into TEST_TB01 (sensor_id,part_id) values(2104,9914);
insert into TEST_TB01 (sensor_id,part_id) values(2105,9915);

TEST_TB02にデータを挿入します。

insert into TEST_TB02 (part_id,elem_id) values(9911,8901);
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);
insert into TEST_TB02 (part_id,elem_id) values(9916,8906);

TEST_TB01のデータを表示する。

TEST_TB02のデータを表示する。

1. SQLでのJOINの使用

TEST_TB01とTEST_TB02は、part_idに基づくJOIN結合を使用して、2つのテーブル（TEST_TB01とTEST_TB02）の同じ結合フィールドの行だけを返します。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

2. SQLでのINNER JOINの使用

TEST_TB01とTEST_TB02は、part_idに基づくINNER JOIN結合を使用して、2つのテーブル（TEST_TB01とTEST_TB02）の同じ結合フィールドの行のみを返します。INNER JOINとJOIN効果は等価です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
INNER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

3. SQLでのLEFT JOINの使用

TEST_TB01とTEST_TB02はpart_idに基づくLEFT JOIN結合を使用しています。左側のテーブル（TEST_TB01）の全ての行と右側のテーブル（TEST_TB02）の結合フィールドが等しい行を返す、いわゆる左結合です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
LEFT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

4. SQLでのLEFT OUTER JOINの使い方

TEST_TB01とTEST_TB02は、左側のテーブル（TEST_TB01）のすべての行と右側のテーブル（TEST_TB02）の同じジョインフィールドの行を返す、LEFT OUTER JOINを使用して、パートIDに基づいて結合されています。

LEFT JOINと同等です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
LEFT OUTER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

5. SQLでのRIGHT JOINの使用

TEST_TB01とTEST_TB02は、part_idに基づいたRIGHT JOINを使用して結合されています。右側のテーブル（TEST_TB02）の全ての行と、左側のテーブル（TEST_TB01）の結合フィールドが等しい行を返す右結合です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
RIGHT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

6. SQLでのFULL JOINの使用

TEST_TB01とTEST_TB02は、part_idに基づくFULL JOIN結合、つまり両方のテーブルから行を返す外部結合を使用しています。LEFT JOIN + RIGHT JOIN すべての行のレコードを返します。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
FULL JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

IV. データが重複している場合のSQL文でのJOINの使用について

SQL文のJOINを使ったデータケースが重複している、つまり、TEST_TB01とTEST_TB02の両テーブルにデータケースが重複している場合です。JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOINを使って、それぞれ検証してみましょう。

TEST_TB01にデータを挿入します。

insert into TEST_TB01 (sensor_id,part_id) values(2101,9911);
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);
insert into TEST_TB01 (sensor_id,part_id) values(2104,9914);
insert into TEST_TB01 (sensor_id,part_id) values(2105,9915);
--create duplicate data
insert into TEST_TB01 (sensor_id,part_id) values(2102,9912);
insert into TEST_TB01 (sensor_id,part_id) values(2103,9913);

TEST_TB02にデータを挿入します。

insert into TEST_TB02 (part_id,elem_id) values(9911,8901);
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);
insert into TEST_TB02 (part_id,elem_id) values(9916,8906);
--create duplicate data
insert into TEST_TB02 (part_id,elem_id) values(9912,8902);
insert into TEST_TB02 (part_id,elem_id) values(9913,8903);

TEST_TB01のデータを表示する。

TEST_TB02のデータを表示する。

1. SQLでのJOINの使用

TEST_TB01とTEST_TB02は、part_idに基づいたJOIN結合を使用して、両方のテーブル（TEST_TB01とTEST_TB02）で結合フィールドが同じ行だけを返します。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

2. SQLでのINNER JOINの使用

TEST_TB01とTEST_TB02は、part_idに基づくINNER JOIN結合を使用して、2つのテーブル（TEST_TB01とTEST_TB02）の同じ結合フィールドの行だけを返します。INNER JOINとJOIN効果は等価です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
INNER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

3. SQLでのLEFT JOINの使用

TEST_TB01とTEST_TB02はpart_idに基づくLEFT JOIN結合を使用しています。左側のテーブル（TEST_TB01）の全ての行と右側のテーブル（TEST_TB02）の結合フィールドが等しい行を返す、いわゆる左結合です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
LEFT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

4. SQLでのLEFT OUTER JOINの使い方

TEST_TB01とTEST_TB02は、左側のテーブル（TEST_TB01）のすべての行と右側のテーブル（TEST_TB02）の同じジョインフィールドの行を返す、LEFT OUTER JOINを使用して、パートIDに基づいて結合されています。

LEFT JOINと同等です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
LEFT OUTER JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

5. SQLでのRIGHT JOINの使用

TEST_TB01とTEST_TB02は、part_idに基づいたRIGHT JOINを使用して結合されています。右側のテーブル（TEST_TB02）の全ての行と、左側のテーブル（TEST_TB01）の結合フィールドが等しい行を返す右結合です。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
RIGHT JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

6. SQLでのFULL JOINの使用

TEST_TB01とTEST_TB02は、part_idに基づくFULL JOIN結合、つまり両方のテーブルから行を返す外部結合を使用しています。LEFT JOIN + RIGHT JOIN すべての行のレコードを返します。

SQL文です。

SELECT
  *
FROM
  TEST_TB01 aa
FULL JOIN TEST_TB02 bb
    ON aa.part_id = bb.part_id
ORDER BY aa.sensor_id ASC;

実行結果です。

V. SQLのJOINを重複データと非重複データの違いで使い分ける

JOIN結合を使用するSQL文において、重複データのケースでJOINを使用すると、結合フィールドの等式が一致するレコードの結果セットは、最初のテーブルの行数に2番目のテーブルの行数を掛けた直積になります。

VI. 解決方法

1. JOIN接続を使用する前に複製を解除する

JOIN接続を行う前に、TEST_TB01とTEST_TB02をビジネスルールに従って別々に複製を解除してください。

2. JOIN接続を先に行い、その後重複排除を行う。

JOINジョインを使用して、ビジネスルールに従ってTEST_TB01とTEST_TB02の結果セットを最初に生成し、次に結果セットの重複を解除します。

3. 推奨事項

本番環境、特にデータ集中型のシナリオでは、JOIN結合を使用する前にテーブルごとに重複排除を行う、最初のアプローチを推奨します。

VII. リレーショナルデータベース検証用テーブル構造

この例は、DataWorks環境（MaxCompute Big Data Platform）で検証しています。つまり、テーブル構造の違いを除けば、リレーショナルデータベースの検証は同じです。

ORACLEデータベースのテーブル構築文で。

create table TEST_TB01
(
  sensor_id NUMBER(16),
  part_id NUMBER(16)
 );
 
 create table TEST_TB02
(
  part_id NUMBER(16),
  elem_id NUMBER(16) 
 );

MySQLデータベースでテーブル文を作成する。

CREATE TABLE TEST_TB01
(
  sensor_id BIGINT,
  part_id BIGINT
 );
 
 CREATE TABLE TEST_TB02
(
  part_id BIGINT,
  elem_id BIGINT 
 );

上記、ありがとうございます。

SQL文のJOINの使い方についての記事は以上です。SQLのJOINの使い方については、スクリプトハウスの過去記事を検索するか、以下の記事を引き続き閲覧してください。