高度な結合技術：LATERAL 結合、セミ結合、アンチ結合

** ## # イントロダクション INNER JOIN と LEFT JOIN は、SQL クエリの大部分を処理できます。しかし、より少数のクラスの問題には、他の [結合タイプ](https://www.stratascratch.com/blog/different-types-of-sql-joins-that-you-must-know?utm_source=blog&utm_medium=click&utm_campaign=kdn+advanced+join+techniques) が必要です**：集計関数（set-returning function）の結果を行ごとにカウントする、別のテーブルでの存在に基づいて行をフィルタリングする、あるいは別のテーブルで一致しない行を返すといったケースです。 これらをきれいに処理するのが、3 つのあまり一般的ではない結合です。LATERAL 結合（LATERAL join）は、FROM クラース内のサブクエリが、同じ FROM クラース内で先行する列を参照することを可能にします。Semi 結合（Semi join）は、別のテーブルで一致が存在する行を返しますが、その行の重複は発生しません。Anti 結合（Anti join）は、別のテーブルで一致が存在しない行を返します。 これらのパターンを実践的にどのように適用するかを探っていきましょう。 ** ![image](https://www.kdnuggets.com/wp-content/uploads/Rosidi_Advanced-Join-Techniques-Lateral-Semi-Anti_3.png) FROM クラース内の LATERAL サブクエリは、同じ FROM クラース内の先行するテーブルの列を参照できます。LATERAL を使用しない場合、FROM 内のサブクエリは独立して評価され、それらの列を認識することはできません。 これは、セット返却関数（set-returning function：入力に対して複数の行を返す関数）を呼び出す際に特に重要です。セット返却関数は SELECT リスト内で呼び出すこともできますが、FROM クラース内の外部テーブルの列に対して行ごとに適用するには、LATERAL が必要です。 一般的なケース:** - 配列カラムに対して unnest() を呼び出し、配列の各要素ごとに 1 行を取得する - 'g' フラグを指定して regexp_matches() を呼び出し、各行ごとのすべての一致箇所を抽出する - FROM 句に相関サブクエリを使用して、グループごとの上位 N 件結果を計算する - 各行の JSON 配列を分割する ## // 例：単語出現回数のカウント **[この Google の質問](https://platform.stratascratch.com/coding/9814-counting-instances-in-text?code_type=1&utm_source=blog&utm_medium=click&utm_campaign=kdn+advanced+join+techniques)** では、contents カラム内の「bull」という単語と「bear」という単語がそれぞれ何回出現しているかを数えるよう求めています。一致は大文字小文字を区別せずに行う必要がありますが、「bullish」や「bearing」のような部分文字列は除外する必要があります。 **データ**: google_file_store テーブルの内容は以下の通りです: filename contents draft1.txt The stock exchange predicts a bull market which would make many investors happy. draft2.txt The stock exchange predicts a bull market... but analysts warn... we are awaiting a bear market. final.txt The stock exchange predicts a bull market... a bear market. As always predicting the future market is uncertain... **コード**: regexp_matches() は一致するたびに 1 行を返します。google_file_store の各行に対してこれを一度実行し、テーブル全体のすべての一致箇所を数えるために、LATERAL を使用して FROM 句に配置します。\m と \M アカーは **[PostgreSQL](https://www.postgresql.org)** の単語境界を示すものであり、これによって「bullish」や「bearing」が除外されます。 SELECT 'bull' AS word, COUNT(*) AS nentry FROM google_file_store, LATERAL regexp_matches(LOWER(contents), '\m(bull)\M', 'g') UNION ALL SELECT 'bear' AS word, COUNT(*) AS nentry FROM google_file_store, LATERAL regexp_matches(LOWER(contents), '\m(bear)\M', 'g'); ## // Output word nentry bull 3 bear 2 ## # Semi Joins (セミジョイン) ** セミジョインは、右テーブルに少なくとも1つの一致が存在する左テーブルの行を返すものであり、各左テーブルの行は最大1回しか現れません。INNER JOIN では、右側に複数の一致がある場合、左テーブルの行が重複して表示されます。しかし、セミジョインではそのような重複は発生しません。 2 つの SQL 実装方法:** - WHERE EXISTS (SELECT 1 FROM ...) - WHERE col IN (SELECT col FROM ...) EXISTS はより一般的な形式です。これは、クエリを書き換えることなく、複数列の結合条件や相関サブクエリを処理できるためです。 ## // Example: Finding High-Value Customers (例：高価値顧客の特定) **[この質問](https://platform.stratascratch.com/coding/2172-customers-with-large-orders?code_type=1&utm_source=blog&utm_medium=click&utm_campaign=kdn+advanced+join+techniques)** では、$100 以上の注文を少なくとも 1 つ行った顧客を見つけ、その顧客 ID と名前を返すよう求めています。 **データ**: online_store_customers および online_store_orders のプレビュー:** customer_id customer_name 1 Alice Johnson 2 Bob Smith 3 Carol Williams … … 10 Jack Anderson order_id customer_id amount status 101 1 150 paid 102 1 200 paid 103 1 75 paid ... ... ... ... 115 9 450 paid **コード**: EXISTS サブクエリは、顧客ごとに $100 を超える注文が存在するかどうかを確認します。SELECT 1 は、EXISTS が行が返ってくるかどうかにのみ関心があり、その中身には関心がないため、この慣習です。 SELECT c.customer_id, c.customer_name FROM online_store_customers c WHERE EXISTS ( SELECT 1 FROM online_store_orders o WHERE o.customer_id = c.customer_id AND o.amount > 100 ); もし INNER JOIN を使用した場合、顧客 1 は一致する注文が 2 つあるため結果に 2 回表示されます。EXISTS は顧客 1 を 1 回のみ返します。 ## // 出力 customer_id customer_name 1 Alice Johnson 2 Bob Smith 3 Carol Williams … … 9 Ivy Taylor ## # アンチジョイン (Anti Joins) **アンチジョインは、右テーブルに一致する行が存在しない場合、左テーブルから行を返します。これはセミジョインの逆です。 2 つの SQL 実装:** - LEFT JOIN ... WHERE right_table.col IS NULL - WHERE NOT EXISTS (SELECT 1 FROM ...) 両方とも同じ結果を生成します。NOT EXISTS は、最新の PostgreSQL バージョンではより良いクエリプランを生成することが多く、読みやすさの点でも直接的です。LEFT JOIN + IS NULL パターンは古くからあるもので、一致しない行についても右側の列が必要となる場合に有用です。 ## // 例：4 月に通話のない無料ユーザー **[この質問](https://platform.stratascratch.com/coding/2018-inactive-free-users?code_type=1&utm_source=blog&utm_medium=click&utm_campaign=kdn+advanced+join+techniques)** は、2020 年 4 月に通話をしなかった無料ユーザーを返すよう求めています。 **データ**: rc_calls と rc_users のプレビュー:** user_id call_id call_date 1218 0 2020-04-19 01:06:00 1554 1 2020-03-01 16:51:00 1857 2 2020-03-29 07:06:00 1525 3 2020-03-07 02:01:00 … … … 1910 39 2020-03-11 08:33:00 user_id status company_id 1218 free 1 1554 inactive 1 1857 free 2 … … … 1884 free 1 **コード**: 日付フィルタは WHERE 句ではなく ON 句に配置されます。この区別こそが、これをアンチ結合（Anti Join）とする理由です。日付フィルタを WHERE に置くと、LEFT JOIN で NULL が生成された行が削除され、結果としてインナー結合（Inner Join）に戻ってしまいます。一方、フィルタを ON 句に配置することで、条件を満たす 4 月の通話がないフリーユーザーも右側に NULL を持つ行として出力されます。その後、IS NULL チェックによってこれらの行のみが保持されます。 SELECT DISTINCT u.user_id FROM rc_users u LEFT JOIN rc_calls c ON u.user_id = c.user_id AND c.call_date BETWEEN '2020-04-01' AND '2020-04-30' WHERE u.status = 'free' AND c.user_id IS NULL; ## // 出力 user_id 1575 1910 ## # 結論 ** ![image](https://www.kdnuggets.com/wp-content/uploads/Rosidi_Advanced-Join-Techniques-Lateral-Semi-Anti_5.png) これら 3 つの結合手法は、インナー結合（INNER JOIN）や左結合（LEFT JOIN）が不適切または誤りとなるケースを解決します:** - LATERAL は、FROM 句内でセット返却関数（set-returning functions）を行ごとに呼び出すための方法です。 - EXISTS を使用すると、重複を引き起こすインナー結合（INNER JOIN）による問題なく、「一致する行」を取得できます。 - NOT EXISTS または LEFT JOIN + IS NULL を組み合わせることで、「一致しない行」をきれいに取得できます。 覚えるべきパターンは短いです。INNER JOIN で望まない行の重複が発生する場合は EXISTS を使い、一致する行がない場合が必要なときは NOT EXISTS または LEFT JOIN + IS NULL を使用します。FROM 句内のサブクエリが外部テーブルの列を参照する必要がある場合は、LATERAL を追加してください。

高度な結合技術：LATERAL 結合、セミ結合、アンチ結合

背景や根拠まで確認しますか？

調べる

選ぶ

サイト