MaxCompute (ODPS) SQL の Join on 条件 - - Alibaba Cloud ドキュメントセンター

MaxCompute (ODPS) SQL の最も一般的な操作の 1 つは結合です。

概要

現在の MaxCompute では、複数の結合タイプが提供されています。


タイプ	意味
Inner join	Association の基準に一致するデータが出力されます。
Left join	左側のテーブルの全レコードと、関連付けるデータと一致する右側のテーブルのレコードが出力されます。右側のテーブルに一致するレコードがない場合、右側のテーブルでは null が補われて出力されます。
Right join	右側のテーブルの全レコードと、関連付けるデータと一致する左側のテーブルのレコードが出力されます。左側のテーブルに一致するレコードがない場合、左側のテーブルでは null が補われて出力されます。
Full join	左右のテーブルのすべてのレコードが出力されます。関連付けるデータがない場合は、関連付けのない側には null が追加されます。
Left Semi Join	左側のテーブルの 1 つのデータについて、Association の基準に一致する行が右側のテーブルにある場合、左側のテーブルが出力されます。
Left Anti Join	左側のテーブルの 1 つのデータについて、右側のテーブルのすべての行が、Association の基準に一致するデータがない場合は、左側のテーブルが出力されます。

注ユーザー定義結合では両方の入力ストリームを指定します。結合のロジックは、ユーザーが実装します (ここでは説明しません)。

シナリオに応じて、さまざまな結合タイプを使用して、対応する Association 操作を実装します。しかし実際の使用プロセスでは、join on 文と where 文とでフィルタリング基準が異なることがユーザーに分かりづらい、またはユーザーはどちらも同等の処理と考えているため、たとえば本番環境でユーザーが次のように書いているのが頻繁に見られます。

A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

ここでのユーザーの目的は、結合操作によって A と B のパーティションのデータを取得することです。

(Select * from a where DS = '20180101 ')
(LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI)  JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key

しかし、異なる結合タイプの場合、2 つは同等でない可能性があり、パーティション条件をプッシュできないだけでなく、フルテーブルスキャンが実行され、正確性の問題を引き起こす可能性があります。以下に、フィルタ条件の簡単な分析を示します。

サブクエリの Where 条件
JOIN ON 条件
JOIN ON 条件の後ろの Where 条件

類似点と相違点

原理

join と where 条件の計算順序から始めます。

(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

たとえば、計算の順序は以下のようになります。

サブクエリ {subquery_where_condition}
結合条件 {on_condition}
結合結果コレクションの計算 {where_condition}

異なる結合タイプの場合、フィルタ文は {subquery_where_condition}、{on_condition}、{where_condition} に配置され、結果は同じになることも、ならないこともあります。以下に説明します。

実験

準備

まずテーブルを構築します。

CREATE TABLE A AS SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);


key	ds
1	20180101
2	20180101
2	20180102

テーブル B

CREATE TABLE B AS SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);


key	ds
1	20180101
3	20180101
2	20180102

デカルト積は次のようになります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
1	20180101	3	20180101
1	20180101	2	20180102
2	20180101	1	20180101
2	20180101	3	20180101
2	20180101	2	20180102
2	20180102	1	20180101
2	20180102	3	20180101
2	20180102	2	20180102

Inner Join

結論としては、{subquery_where_condition}、{on_condition}、{where_condition} については、フィルタ条件は同等です。

inner join の処理ロジックでは、左右のテーブルのデカルト積を生成して、on 式を満たす移動行の出力を選択します。

最初のケースでは、サブクエリがフィルタされます。

Select a. *, B .*
From
(Select * from a where DS = '20180101 ')
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

これは非常に単純で、1 件の結果のみが返されます。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

2 番目のケースでは、JOIN 条件がフィルタされます。

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

デカルト積の結果は 9 件あり、on の条件を満たす結果は 1 件だけです。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

3 番目のケースでは、JOIN の後に where 条件がフィルタされます。

Select a. *, B .*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

たとえば、ON a.key = b.key を満たすデカルト積の結果は 9 件あり、それぞれのキーに 3 件の結果があります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180102	2	20180102
2	20180101	2	20180102

この結果を再度 A.ds='20180101' and B.ds='20180101' でフィルタすると、結果は 1 件のみになります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

ご覧のとおり、3 つの異なる場所にフィルタ条件を配置することによって、3 件の異なる結果が得られます。

Left join

結論としては、フィルタリング条件 {subquery_where_condition}、{on_condition}、{where_condition} は必ずしも同等ではありません。

左側のテーブルのフィルタ基準について、{subquery_where_condition} と {where_condition} に配置されたものは同等です。

右側のテーブルのフィルタ基準について、{subquery_where_condition} と {on_condition} に配置されたものは同等です。

left join の処理ロジックは、左右のテーブルのデカルト積を作成してから、on 式を満たす移動行を出力します。on 式を満たさない左側のテーブルの行については、左側のテーブルを出力し、右側のテーブルは null を補います。

最初のケースでは、サブクエリがフィルタされます。

SELECT A.*, B.*
From
(SELECT * FROM A WHERE ds='20180101') A
LEFT JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

フィルタリングした後、左側に 2 件、右側に 1 件、結果に 2 件があります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL

2 番目のケースでは、JOIN 条件がフィルタされます。

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

デカルト積の結果は 9 件あり、デカルト積の結果は 9 件あり、on の条件を満たすのは 1 件だけです。左側のテーブルの残りの 2 つの出力については、左側のテーブルは null です。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
2	20180102	NULL	NULL

3 番目のケースでは、JOIN の後に where 条件がフィルタされます。

SELECT A.*, B.*
FROM A JOIN B
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

たとえば、ON a.key = b.key を満たすデカルト積の結果は 9 件あり、それぞれのキーに 3 件の結果があります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102

この結果を再度 A.ds='20180101' and B.ds='20180101' でフィルタすると、結果は 1 件のみになります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

ご覧のとおり、3 つの異なる場所にフィルタ条件を配置することによって、3 件の異なる結果が得られます。

Right join
right join と left join は似ていて、左右のテーブルの違いだけです。結論としては、フィルタリング条件 {subquery_where_condition}、{on_condition}、{where_condition} は必ずしも同等ではありません。右側のテーブルのフィルタ基準について、{subquery_where_condition} と {where_condition} に配置されたものは同等です。左側のテーブルのフィルタ基準について、{subquery_where_condition} と {on_condition} に配置されたものは同等です。

Full join

結論としては、{subquery_where_condition}、{on_condition}、{where_condition} に書かれたフィルタ条件は同等ではありません

full join の処理ロジックは、左右のテーブルのデカルト積を作成してから、on 式を満たす移動行を出力します。on 式を満たさない左右両側のテーブルの行に対して、他方に null を入れます。

最初のケースでは、サブクエリがフィルタされます。

SELECT A.*, B.*
From
(SELECT * FROM A WHERE ds='20180101') A
FULL JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

フィルタリングした後、左側に 2 件、右側に 2 件、および右側に 3 件があります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
NULL	NULL	3	20180101

2 番目のケースでは、JOIN 条件がフィルタされます。

SELECT A.*, B.*
FROM A FULL JOIN B':
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

デカルト積の結果は 9 件あり、on の条件を満たす結果は 1 件だけです。左側のテーブルの残りの 2 件の出力については、左側のテーブルは null です。右側のテーブルの残りの 2 件の出力、右側のテーブル、および左側のテーブルに、null が追加されます。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
2	20180102	NULL	NULL
NULL	NULL	3	20180101
NULL	NULL	2	20180102

3 番目のケースでは、JOIN の後に where 条件がフィルタされます。

SELECT A.*, B.*
FROM A FULL JOIN B:
ON a.key = b.key
WHERE A.ds='20180101' and B.ds='20180101';

たとえば、a.key = b.key を満たすデカルト積の結果は 9 件あり、それぞれのキーに 3 件の結果があります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102

JOIN の反対側のデータが出力され、反対側に NULL が追加され、結果は次のとおりです。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102
NULL	NULL	3	20180101

この結果を再度 A.ds='20180101' and B.ds='20180101' でフィルタすると、結果は 1 件のみになります。


a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

ご覧のとおり、LEFT JOIN のように、3 つの異なる結果があります。

Left Semi Join

結論としては、 {subquery_where_condition}、{on_condition}、{where_condition} に書かれたフィルタ条件は同等ではありません

LEFT SEMI Join の処理ロジックでは、左側のテーブルの各レコードを、右側のテーブルと一致させ、一致が成功した場合、左側のテーブルが出力されます。ここで注意すべきことは、左側のテーブルのみが出力されるため、JOIN 後の where 条件には右側のテーブルのフィルタ条件を記述できないことです。 Left semi join は通常 exists のセマンティクスを実装するために使われます。

最初のケースでは、サブクエリがフィルタされます。

SELECT A.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

フィルタリングした後、左右に 2 件あり、最終的に a.key = b.key に一致するキーは 1 つだけです。


a.key	a.ds
1	20180101

2 番目のケースでは、JOIN 条件がフィルタされます。

SELECT A.*
FROM A LEFT SEMI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

左側の 3 つのレコードについては、on 条件を満たす結果は 1 件だけです。


a.key	a.ds
1	20180101

3 番目のケースでは、JOIN の後に where 条件がフィルタされます。

Select .*
FROM A LEFT SEMI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

左側の on 条件を満たすものが 1 件あります。


a.key	a.ds
1	20180101

A.では、この結果を再度 A.ds='20180101' でフィルタすると、結果は 1 件のままです。


a.key	a.ds
1	20180101

ご覧のとおり、left semi join と inner join は似ています。フィルタ条件を配置する場所にかかわらず、結果は同じでした。

Left Anti Join

結論としては、{subquery_where_condition}、{on_condition}、{where_condition} に書かれたフィルタ条件は同等ではありません。

左側のテーブルのフィルタ基準について、{subquery_where_condition} と {where_condition} に配置されたものは同等です。

右側のテーブルのフィルタ基準について、{subquery_where_condition} と{on_condition} に配置されたものは同等ですが、右側のテーブルの式 {where_condition} には配置できません。

left anti join の処理ロジックは、左側のテーブルの各レコードを、右側のテーブルと一致させ、右側のテーブルに一致するレコードがない場合は、一致が成功し、左側のテーブルが出力されます。同様に、左側のテーブルのみが出力されるため、JOIN 後の where 条件には右側のテーブルのフィルタ条件を記述できません。 LEFT SEMI JOIN は not exists のセマンティクスを実装するためによく使われます。

最初のケースでは、サブクエリがフィルタされます。

SELECT A.*
From
(SELECT * FROM A WHERE ds='20180101') A
LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

フィルタリングした後、左側に 2 件、右側に 2 件、および結果に 2 件があります。


a.key	a.ds
2	20180101

2 番目のケースでは、JOIN 条件がフィルタされます。

SELECT A.*
FROM A LEFT ANTI JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

左側の 3 つのレコードについて、最初のレコードのみが ON 条件を満たす結果になるため、残りの 2 つのレコードが出力されます。


a.key	a.ds
2	20180101
2	20180102

3 番目のケースでは、JOIN 条件の後に where 条件がフィルタされます。

SELECT A.*
FROM A LEFT ANTI JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key
WHERE A.ds='20180101';

左側の ON に渡す on 条件が 2 つあります。


a.key	a.ds
2	20180101
2	20180102

この結果を再度 A.ds='20180101' でフィルタすると、結果は 1 です。


a.key	a.ds
2	20180101

ご覧のとおり、LEFT ANTI JOIN では、フィルタ条件は JOIN ON 条件とその前後の where 条件に配置され、結果は異なります。

上記は、一般的なシナリオ向けのさまざまな記述方法を、特定の推論処理なしでテストしたものです。互いに同等ではない式を含むシナリオの場合、より複雑になります。関心のある方は、独自の書き方を作成することができます。

オンラインステータス

これらの結果は、SQL の標準セマンティックパターンから派生したものです。ユーザーの中には、オンライン環境で同じ文の結果が予想と一致しないことに気づく人がいるかもしれません。これは、歴史的理由と互換性の考慮事項が原因です。 Outer Join の実装では、プロジェクトレベルでフラグが設定され、odps.sql.outerjoin.supports.filters が呼び出されます。false が設定された場合は、Outer Join の on 条件がフィルタリング条件でサポートされないことを示します。{on_condition} の記述は {subquery_where_condition} のように扱われますが、これは標準的な動作ではありません。 2 つのプロジェクトを切り替えると、同じ SQL が 2 つのプロジェクトで異なる動作をすることがあります。

標準の SQL セマンティクスに従って SQL を記述することができれば、以降の SQL の移植性を保証するためにも望ましいです。

プロジェクトの設定を表示するには、http://adminconsole.odps.aliyun-inc.com/inn.view の project administration で該当するプロジェクトを見つけて、プロパティを表示します。

結論

異なる場所のフィルタ条件のセマンティクスは、ユーザーによって大きく異なる可能性があり、単にデータをフィルタリングして結合する場合は、次の点に注意する必要があります。

inner join と left semi join は式の両側に書きます。
Left join と left anti join では、左側のテーブルのフィルタ基準は {subquery_where_condition} または {where_condition} に入れ、左側のテーブルのフィルタ基準は {subquery_where_condition} または {on_condition} のいずれかに配置します。
Right join は left join の正反対で、右側のテーブルのフィルタ基準は {subquery_where_condition} または {where_condition} のいずれかに配置し、左側のテーブルのフィルタ基準は {subquery_where_condition} または {on_condition} に配置します。
Full outer join では、{subquery_where_condition} にのみ配置します。

ルールが複雑だと思う場合は、毎回フィルタ基準をサブクエリへ書き込むことが最善の方法です。