翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # セット演算子 *セット演算子*は、2 つの個別のクエリ式の結果を比較およびマージするために使用されます。 AWS Clean Rooms Spark SQL は、次の表に示す以下のセット演算子をサポートしています。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ja_jp/clean-rooms/latest/sql-reference/UNION.html) 例えば、ウェブサイトで購入者と販売者の両方を兼ねているが、ユーザー名が別々の列または表に格納されているユーザーを確認するには、これら 2 種類のユーザーの積集合**を求めます。購入者ではあるが販売者ではないウェブユーザーを確認するには、EXCEPT 演算子を使用すると、2 つユーザーリストの差**を見つけることができます。役割とは無関係に、すべてのユーザーのリストを作成する場合、UNION 演算子を使用できます。 **注記** ORDER BY、LIMIT、SELECT TOP、および OFFSET 句は、UNION、UNION ALL、INTERSECT、および EXCEPT セット演算子によってマージされたクエリ式では使用できません。 **Topics** + [構文](#UNION-synopsis) + [パラメータ](#UNION-parameters) + [セット演算の評価の順番](#UNION-order-of-evaluation-for-set-operators) + [使用に関する注意事項](#UNION-usage-notes) + [UNION クエリの例](example_union_query.md) + [UNION ALL クエリの例](example_unionall_query.md) + [INTERSECT クエリの例](example_intersect_query.md) + [EXCEPT クエリの例](Example_EXCEPT_query.md) ## 構文 ``` subquery1 { { UNION [ ALL | DISTINCT ] | INTERSECT [ ALL | DISTINCT ] | EXCEPT [ ALL | DISTINCT ] } subquery2 } [...] } ``` ## パラメータ *subquery1、subquery2* 選択リストの形式で、UNION、UNION ALL、INTERSECT、INTERSECT ALL、EXCEPT、または EXCEPT ALL 演算子に続く 2 番目のクエリ式に対応するクエリ式。2 つの式は、互換性のあるデータ型の出力列を同数含んでいる必要があります。そうでない場合、2 つの結果セットの比較とマージはできません。設定オペレーションでは、異なるカテゴリのデータ型間の暗黙的な変換は許可されません。詳細については、「[型の互換性と変換](s_Type_conversion.md)」を参照してください。 クエリ式の数を上限なしに含むクエリを構築して、そのクエリを任意の組み合わせで UNION、INTERSECT、および EXCEPT 演算子に関連付けることができます。例えば、テーブル T1、T2、および T3 に互換性のある列セットが含まれていると想定した場合、次のクエリ構造は有効です。 ``` select * from t1 union select * from t2 except select * from t3 ``` UNION [ALL \$1 DISTINCT] 行が片方の式から生成されたか、両方の式から生成されたかにかかわらず、2 つのクエリ式からの行を返す演算を設定します。 交差 [すべて \$1 個別] 2 つのクエリ式から生成される行を返す演算を設定します。両方の式によって返されない行は破棄されます。 [ALL \$1 DISTINCT] を除く 2 つのクエリ式の一方から生成される行を返す演算を設定します。結果を制限するため、行は最初の結果テーブルに存在し、2 番目のテーブルには存在しない必要があります。 EXCEPT ALL は結果行から重複を削除しません。 MINUS と EXCEPT はまったく同じ意味です。 ## セット演算の評価の順番 UNION および EXCEPT セット演算子は左結合です。優先順位の決定でかっこを指定しなかった場合、これらのセット演算子の組み合わせは、左から右に評価されます。例えば、次のクエリでは、T1 と T2 の UNION が最初に評価され、次に UNION の結果に対して、EXCEPT 演算が実行されます。 ``` select * from t1 union select * from t2 except select * from t3 ``` 同じクエリ内で演算子の組み合わせを使用した場合、INTERSECT 演算子は UNION および EXCEPT よりも優先されます。例えば、次のクエリは T2 と T3 の積集合を評価し、その結果を T1 を使って結合します。 ``` select * from t1 union select * from t2 intersect select * from t3 ``` かっこを追加することで、評価の順番を変更することができます。次のケースでは、T1 と T2 の結合結果と T3 の積集合を求めます。このクエリでは異なる結果が生成されます。 ``` (select * from t1 union select * from t2) intersect (select * from t3) ``` ## 使用に関する注意事項 + セット演算クエリの結果で返される列名は、最初のクエリ式のテーブルからの列名 (またはエイリアス) です。これらの列名は、列内の値はセット演算子の両方のテーブルから生成されるという点で誤解を生む可能性があるため、結果セットには意味のあるエイリアスを付けることをお勧めします。 + セット演算子クエリが 10 進数の結果を返した場合、同じ精度とスケールで対応する結果列を返すように奨励されます。例えば、T1.REVENUE が DECIMAL(10,2) 列で T2.REVENUE が DECIMAL(8,4) 列の次のクエリでは、DECIMAL(12,4) への結果も 10 進数であることが奨励されます。 ``` select t1.revenue union select t2.revenue; ``` スケールは `4` になります。2 つの列の最大のスケールです。精度は `12` です。T1.REVENUE は小数点の左側に 8 桁必要であるからです (12 - 4 = 8)。このような奨励により、UNION の両側からのすべての値が結果に適合します。64 ビットの値の場合、最大結果精度は 19 で、最大結果スケールは 18 です。128 ビットの値の場合、最大結果精度は 38 で、最大結果スケールは 37 です。 結果のデータ型が AWS Clean Rooms 精度とスケール制限を超えると、クエリはエラーを返します。 + 集合演算で 2 行が同一として扱われるのは、対応する列のペアごとに、2 つのデータ値が等しい**またはどちらも NULL** である場合です。例えば、テーブル T1 と T2 の両方に 1 つの列と 1 つの行が含まれていて、両方のテーブルでその行が NULL の場合、これらのテーブルに INTERSECT 演算に実行すると、その行が返されます。 # UNION クエリの例 次の UNION クエリでは、SALES テーブルの行が、LISTING テーブルの行とマージされます。各テーブルからは 3 つの互換性のある列が選択されます。この場合、対応する列には同じ名前とデータ型が与えられます。 ``` select listid, sellerid, eventid from listing union select listid, sellerid, eventid from sales listid | sellerid | eventid --------+----------+--------- 1 | 36861 | 7872 2 | 16002 | 4806 3 | 21461 | 4256 4 | 8117 | 4337 5 | 1616 | 8647 ``` 次の例では、どのクエリ式が結果セットの各行を生成したかを確認できるように、UNION クエリの出力にリテラル値を追加する方法を示します。このクエリは、最初のクエリ式からの行を (販売者を意味する) 「B」として識別し、2 番目のクエリ式からの行を (購入者を意味する) 「S」として識別します。 このクエリは 10,000 ドル以上のチケット取引の販売者と購入者を識別します。UNION 演算子の両側の 2 つのクエリ式の違いは、SALES テーブルの結合列だけです。 ``` select listid, lastname, firstname, username, pricepaid as price, 'S' as buyorsell from sales, users where sales.sellerid=users.userid and pricepaid >=10000 union select listid, lastname, firstname, username, pricepaid, 'B' as buyorsell from sales, users where sales.buyerid=users.userid and pricepaid >=10000 listid | lastname | firstname | username | price | buyorsell --------+----------+-----------+----------+-----------+----------- 209658 | Lamb | Colette | VOR15LYI | 10000.00 | B 209658 | West | Kato | ELU81XAA | 10000.00 | S 212395 | Greer | Harlan | GXO71KOC | 12624.00 | S 212395 | Perry | Cora | YWR73YNZ | 12624.00 | B 215156 | Banks | Patrick | ZNQ69CLT | 10000.00 | S 215156 | Hayden | Malachi | BBG56AKU | 10000.00 | B ``` 次の例では、重複行が検出された場合、その重複行を結果に保持する必要があるため、UNION ALL 演算子を使用します。一連の特定イベント ID では、クエリは各イベントに関連付けられているセールスごとに 0 行以上の行を返し、そのイベントのリスティングごとに 0 行または 1 行を返します。イベント ID は、LISTING テーブルと EVENT テーブルの各行に対して一意ですが、SALES テーブルのイベント ID とリスティング ID の同じ組み合わせに対して、複数のセールスが存在することがあります。 結果セットの 3 番目の列は、行のソースを特定します。その行が SALES テーブルからの行だった場合、SALESROW 列に "Yes" というマークが付きます。(SALESROW は SALES.LISTID のエイリアスです。) その行が LISTING テーブルからの行だった場合、SALESROW 列に "No" というマークが付きます。 この場合、リスティング 500、イベント 7787 の結果セットは、3 つの行から構成されます。つまり、このリスティングとイベントの組み合わせに対して、3 つの異なる取引が実行されたということです。他の 2 つのリスティング (501 と 502) では販売はありません。このため、これらのリスト ID に対してクエリが生成した唯一の行は LISTING テーブル (SALESROW = 'No') から生成されます。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` ALL キーワードを付けずに同じクエリを実行した場合、結果には、セールス取引の 1 つだけが保持されます。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` # UNION ALL クエリの例 次の例では、重複行が検出された場合、その重複行を結果に保持する必要があるため、UNION ALL 演算子を使用します。一連の特定イベント ID では、クエリは各イベントに関連付けられているセールスごとに 0 行以上の行を返し、そのイベントのリスティングごとに 0 行または 1 行を返します。イベント ID は、LISTING テーブルと EVENT テーブルの各行に対して一意ですが、SALES テーブルのイベント ID とリスティング ID の同じ組み合わせに対して、複数のセールスが存在することがあります。 結果セットの 3 番目の列は、行のソースを特定します。その行が SALES テーブルからの行だった場合、SALESROW 列に "Yes" というマークが付きます。(SALESROW は SALES.LISTID のエイリアスです。) その行が LISTING テーブルからの行だった場合、SALESROW 列に "No" というマークが付きます。 この場合、リスティング 500、イベント 7787 の結果セットは、3 つの行から構成されます。つまり、このリスティングとイベントの組み合わせに対して、3 つの異なる取引が実行されたということです。他の 2 つのリスティング (501 と 502) では販売はありません。このため、これらのリスト ID に対してクエリが生成した唯一の行は LISTING テーブル (SALESROW = 'No') から生成されます。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` ALL キーワードを付けずに同じクエリを実行した場合、結果には、セールス取引の 1 つだけが保持されます。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No ``` # INTERSECT クエリの例 次の例を最初の UNION の例と比較してみます。2 つの例の違いは使われたセット演算子だけですが、結果は大きく異なります。1 つの行だけが同じになります。 ``` 235494 | 23875 | 8771 ``` これは、両方のテーブルから検出された 5 つの行の制限された結果の唯一の行です。 ``` select listid, sellerid, eventid from listing intersect select listid, sellerid, eventid from sales listid | sellerid | eventid --------+----------+--------- 235494 | 23875 | 8771 235482 | 1067 | 2667 235479 | 1589 | 7303 235476 | 15550 | 793 235475 | 22306 | 7848 ``` 次のクエリでは、3 月にニューヨーク市とロサンジェルスの両方で発生した (チケットが販売された) イベントを検索します。2 つのクエリ式の違いは、VENUECITY 列の制約です。 ``` select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='Los Angeles' intersect select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='New York City'; eventname ---------------------------- A Streetcar Named Desire Dirty Dancing Electra Running with Annalise Hairspray Mary Poppins November Oliver! Return To Forever Rhinoceros South Pacific The 39 Steps The Bacchae The Caucasian Chalk Circle The Country Girl Wicked Woyzeck ``` # EXCEPT クエリの例 データベースの CATEGORY テーブルには、次の 11 行が含まれています。 ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows) ``` CATEGORY\$1STAGE テーブル (ステージングテーブル) には、1 つの追加行が含まれていると想定します。 ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts 12 | Concerts | Comedy | All stand up comedy performances (12 rows) ``` 2 つのテーブル間の違いを返します。つまり、CATEGORY テーブル内の行ではなく、CATEGORY\$1STAGE テーブル内の行が返されるということです。 ``` select * from category_stage except select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` 次の同等のクエリでは、同義語の MINUS を使用します。 ``` select * from category_stage minus select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` SELECT 式の順番を逆にすると、クエリは行を返しません。