翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # SELECT SELECT コマンドは、テーブルおよびユーザー定義関数から行を返します。 AWS Clean Rooms Spark SQL では、次の SELECT SQL コマンド、句、およびセット演算子がサポートされています。 **Topics** + [SELECT list](sql-function-select-list-spark.md) + [WITH 句](WITH_clause.md) + [FROM 句](FROM_clause30.md) + [JOIN 句](join-clause.md) + [WHERE 句](WHERE_clause.md) + [VALUES 句](VALUES.md) + [GROUP BY 句](GROUP_BY_clause.md) + [HAVING 句](HAVING_clause.md) + [セット演算子](UNION.md) + [ORDER BY 句](ORDER_BY_clause.md) + [サブクエリの例](Subquery_examples.md) + [相関性のあるサブクエリ](correlated_subqueries.md) 構文、引数、およびいくつかの例は、[Apache Spark SQL リファレンス](https://spark.apache.org/docs/latest/api/sql/)から取得されます。 # SELECT list SELECT list は、クエリに返させる列、関数、および式を指定します。このリストは、クエリの出力を表しています。 ## 構文 ``` SELECT [ DISTINCT ] | expression [ AS column_alias ] [, ...] ``` ## パラメータ DISTINCT 1 つまたは複数の列の一致する値に基づいて、結果セットから重複する行を削除するオプション。 *expression* クエリによって参照されるテーブル内に存在する 1 つまたは複数の列から構成される式。式には、SQL 関数を含めることができます。例えば、次のようになります。 ``` coalesce(dimension, 'stringifnull') AS column_alias ``` AS column\$1alias 最終的な結果セットに使われる列のテンポラリ名。AS キーワードはオプションです。例えば、次のようになります。 ``` coalesce(dimension, 'stringifnull') AS dimensioncomplete ``` シンプルな列名ではない式に対して、エイリアスを指定しない場合、結果セットはその列に対してデフォルト名を適用します。 **注記** エイリアスは、ターゲットリストで定義された直後に認識されます。同じターゲットリストの後に定義された他の式でエイリアスを使用することはできません。 # WITH 句 WITH 句は、クエリ内の SELECT リストに先行するオプション句です。WITH 句は、1 つまたは複数の common\$1table\$1expressions** を定義します。各共通テーブル式 (CTE) は、ビュー定義に似ている一時テーブルを定義します。これらの一時テーブルは、FROM 句で参照できます。それらは、所属するクエリが実行されている間にのみ使用されます。WITH 句内の各 CTE は、テーブル名、列名のオプションリスト、およびテーブルに対して評価を実行するクエリ表現 (SELECT ステートメント) を指定します。 WITH 句のサブクエリは、単一のクエリ実行中に、使用可能なテーブルを効率的に定義します。SELECT ステートメントの本文内でサブクエリを使用することで、すべてのケースで同じ結果を実現できますが、WITH 句のサブクエリの方が、読み書きが簡単になることがあります。可能な場合は、複数回参照される、WITH 句のサブクエリは、一般的な副次式として最適化されます。つまり、一度 WITH サブクエリを評価すると、その結果を再利用することができるということです。(一般的な副次式は、WITH 句内で定義される副次式に制限されない点に注意してください。) ## 構文 ``` [ WITH common_table_expression [, common_table_expression , ...] ] ``` *common\$1table\$1expression* は、非再帰的にすることもできます。非再帰形式は次のとおりです。 ``` CTE_table_name AS ( query ) ``` ## パラメータ common\$1table\$1expression** [FROM 句](FROM_clause30.md) で参照できる一時テーブルを定義し、それが属するクエリの実行中にのみ使用されます。 CTE\$1table\$1name** WITH 句のサブクエリの結果を定義する一時テーブルの一意な名前。単一の WITH 句内で重複する名前を使用することはできません。各サブクエリには、[FROM 句](FROM_clause30.md) で参照可能なテーブル名を付ける必要があります。 query** が AWS Clean Rooms サポートする SELECT クエリ。「[SELECT](sql-commands-select-spark.md)」を参照してください。 ## 使用に関する注意事項 次の SQL ステートメントで WITH 句を使用できます。 + SELECT、 WITH、UNION、UNION ALL、INTERSECT、INTERSECT ALL、EXCEPT、または EXCEPT ALL WITH 句を含んでいるクエリの FROM 句が、WITH 句によって定義されたテーブルを参照していない場合、含まれている WITH 句は無視された上でクエリは通常どおり実行されます。 WITH 句のサブクエリで定義されたテーブルは、WITH 句が開始した SELECT クエリの範囲でのみ参照可能です。例えば、このようなテーブルは、SELECT リスト、WHERE 句、または HAVING 句内のサブクエリの FROM 句で参照できます。サブクエリ内で WITH 句を使用し、メインクエリまたは別のサブクエリの FROM 句でそのテーブルを参照することはできません。このクエリパターンを使用すると、WITH 句のテーブルに対して、`relation table_name doesn't exist`という形式のエラーメッセージが発生します。 WITH 句のサブクエリ内で、別の WITH 句を指定することはできません。 WITH 句のサブクエリによって定義されたテーブルに対して、前方参照を作成することはできません。例えば次のクエリでは、テーブル W1 の定義内でテーブル W2 への前方参照を設定しているため、エラーが帰されます。 ``` with w1 as (select * from w2), w2 as (select * from w1) select * from sales; ERROR: relation "w2" does not exist ``` ## 例 次の例では、WITH 句を含む最もシンプルなケースを示します。VENUECOPY という名前の WITH クエリは、VENUE テーブルからすべての行を選択します。次にメインクエリでは、VENUECOPY からすべての行を選択します。VENUECOPY テーブルは、このクエリの有効期間中だけ存在します。 ``` with venuecopy as (select * from venue) select * from venuecopy order by 1 limit 10; ``` ``` venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 0 2 | Columbus Crew Stadium | Columbus | OH | 0 3 | RFK Stadium | Washington | DC | 0 4 | CommunityAmerica Ballpark | Kansas City | KS | 0 5 | Gillette Stadium | Foxborough | MA | 68756 6 | New York Giants Stadium | East Rutherford | NJ | 80242 7 | BMO Field | Toronto | ON | 0 8 | The Home Depot Center | Carson | CA | 0 9 | Dick's Sporting Goods Park | Commerce City | CO | 0 v 10 | Pizza Hut Park | Frisco | TX | 0 (10 rows) ``` 次の例では、VENUE\$1SALES と TOP\$1VENUES という名前の 2 つのテーブルを生成する WITH 句を示します。2 番目の WITH 句テーブルは最初のテーブルから選択します。次に、メインクエリブロックの WHERE 句には、TOP\$1VENUES テーブルを制約するサブクエリが含まれています。 ``` with venue_sales as (select venuename, venuecity, sum(pricepaid) as venuename_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid group by venuename, venuecity), top_venues as (select venuename from venue_sales where venuename_sales > 800000) select venuename, venuecity, venuestate, sum(qtysold) as venue_qty, sum(pricepaid) as venue_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid and venuename in(select venuename from top_venues) group by venuename, venuecity, venuestate order by venuename; ``` ``` venuename | venuecity | venuestate | venue_qty | venue_sales ------------------------+---------------+------------+-----------+------------- August Wilson Theatre | New York City | NY | 3187 | 1032156.00 Biltmore Theatre | New York City | NY | 2629 | 828981.00 Charles Playhouse | Boston | MA | 2502 | 857031.00 Ethel Barrymore Theatre | New York City | NY | 2828 | 891172.00 Eugene O'Neill Theatre | New York City | NY | 2488 | 828950.00 Greek Theatre | Los Angeles | CA | 2445 | 838918.00 Helen Hayes Theatre | New York City | NY | 2948 | 978765.00 Hilton Theatre | New York City | NY | 2999 | 885686.00 Imperial Theatre | New York City | NY | 2702 | 877993.00 Lunt-Fontanne Theatre | New York City | NY | 3326 | 1115182.00 Majestic Theatre | New York City | NY | 2549 | 894275.00 Nederlander Theatre | New York City | NY | 2934 | 936312.00 Pasadena Playhouse | Pasadena | CA | 2739 | 820435.00 Winter Garden Theatre | New York City | NY | 2838 | 939257.00 (14 rows) ``` 次の 2 つの例は、WITH 句サブクエリに基づいた、テーブル参照の範囲に関するルールをデモンストレーションしています。最初のクエリは実行されますが、2 番目のクエリは予想どおりのエラーが発生して失敗します。最初のクエリには、メインクエリの SELECT リスト内に WITH 句サブクエリが存在します。WITH 句によって定義されるテーブル (HOLIDAYS) は、SELECT リストのサブクエリの FROM 句で参照されます。 ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join date on sales.dateid=date.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; caldate | daysales | dec25sales -----------+----------+------------ 2008-12-25 | 70402.00 | 70402.00 2008-12-31 | 12678.00 | 70402.00 (2 rows) ``` 2 番目のクエリは SELECT リストのサブクエリ内だけでなく、メインクエリ内の HOLIDAYS テーブルを参照しようとしたため、失敗しました。メインクエリの参照は範囲外です。 ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join holidays on sales.dateid=holidays.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; ERROR: relation "holidays" does not exist ``` # FROM 句 クエリ内の FROM 句は、データの選択下のテーブル参照 (テーブル、ビュー、サブクエリ) を一覧表示します。複数のテーブル参照が一覧表示されている場合、FROM 句または WHERE 句のいずれかの適切な構文を使って、テーブル参照を結合する必要があります。結合基準を指定していない場合、クエリはクロス結合 (デカルト積) として処理されます。 **Topics** + [構文](#FROM_clause30-synopsis) + [パラメータ](#FROM_clause30-parameters) + [使用に関する注意事項](#FROM_clause_usage_notes) ## 構文 ``` FROM table_reference [, ...] ``` ここで *table\$1reference* は、次のいずれかになります。 ``` with_subquery_table_name | table_name | ( subquery ) [ [ AS ] alias ] table_reference [ NATURAL ] join_type table_reference [ USING ( join_column [, ...] ) ] table_reference [ INNER ] join_type table_reference ON expr ``` ## パラメータ with\$1subquery\$1table\$1name** [WITH 句](WITH_clause.md) のサブクエリで定義されるテーブル。 table\$1name** テーブルまたはビューの名前。 alias** テーブルまたはビューの一時的な代替名。エイリアスは、サブクエリから生成されたテーブルに対して提供する必要があります。他のテーブル参照では、エイリアスはオプションです。AS キーワードは常にオプションです。テーブルエイリアスは、WHERE 句など、クエリの別の部分のテーブルを識別するため、便利なショートカットを提供します。 例えば、次のようになります。 ``` select * from sales s, listing l where s.listid=l.listid ``` テーブルエイリアスを定義した場合、クエリでそのテーブルを参照するにはエイリアスを使用する必要があります。 例えば、クエリが `SELECT "tbl"."col" FROM "tbl" AS "t"` の場合、テーブル名は基本的に上書きされているため、クエリは失敗します。この場合の有効なクエリは `SELECT "t"."col" FROM "tbl" AS "t"` です。 column\$1alias** テーブルまたはビュー内の列に対する一時的な代替名。 subquery** テーブルに対して評価を実行するクエリ式。テーブルは、クエリの有効期間中のみ存在し、通常は名前またはエイリアス**が与えられます。ただし、エイリアスは必須ではありません。また、サブクエリから生成されたテーブルに対して、列名を定義することもできます。サブクエリの結果を他のテーブルに結合する場合、および列をクエリ内のどこかで選択または拘束する場合、列のエイリアスの命名は重要です。 サブクエリには ORDER BY 句が含まれることがありますが、LIMIT または OFFSET 句も併せて指定しない場合、この句には効力がありません。 NATURAL 2 つのテーブル内で同じ名前を付けられた列のペアをすべて結合列として、自動的に使用する結合を定義します。明示的な結合条件は必要ありません。例えば、CATEGORY と EVENT の両方のテーブルに CATID という名前の列が存在する場合、これらのテーブルの NATURAL 結合は CATID 列による結合です。 NATURAL 結合を指定しても、結合対象のテーブルに同じ名前の列ペアが存在しない場合、クエリはデフォルト設定のクロス結合になります。 join\$1type** 以下のいずれかの結合タイプを指定します。 + [INNER] JOIN + LEFT [OUTER] JOIN + RIGHT [OUTER] JOIN + FULL [OUTER] JOIN + CROSS JOIN クロス結合は非限定の結合で、2 つの表のデカルト積を返します。 内部結合と外部結合は限定結合です。これらの結合は、FROM 句の ON または USING 構文、または WHERE 句条件を使った (Natural 結合での) 黙示的な結合です。 内部結合は、結合条件、また結合列のリストに基づいて、一致する行だけを返します。外部結合は、同等の内部結合が返すすべての行に加え、「左側の」表、「右側の」表、または両方の表から一致しない行を返します。左の表は最初に一覧表示された表で、右の表は 2 番目に一覧表示された表です。一致しない行には、出力列のギャップを埋めるため、NULL が含まれます。 ON join\$1condition** 結合列を ON キーワードに続く条件として記述する、結合タイプの指定。次に例を示します。 ``` sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid ``` USING ( join\$1column** [, ...] ) 結合列をかっこで一覧表示する結合の指定タイプ。複数の結合列を指定する場合、カンマによって区切ります。USING キーワードは、リストより前に指定する必要があります。例: ``` sales join listing using (listid,eventid) ``` ## 使用に関する注意事項 列を結合するには、データ型に互換性がなければなりません。 NATURAL または USING 結合は、中間結果セットの結合列の各ペアの一方だけを保持します。 ON 構文を使った結合は、中間結果セットの両方の結合列を保持します。 [WITH 句](WITH_clause.md) も参照してください。 # JOIN 句 SQL JOIN 句は、共通のフィールドに基づいて 2 つ以上のテーブルのデータを結合するために使用されます。結果は、指定した結合方法によって変わる場合もあります。左と右の外部結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したどちらかのテーブルの値を保持します。 JOIN タイプと結合条件の組み合わせによって、最終結果セットに含まれる行が決まります。SELECT 句と WHERE 句は、返される列と行のフィルタリング方法を制御します。さまざまな JOIN タイプとそれらを効果的に使用する方法を理解することは、複数のテーブルからのデータを柔軟かつ強力な方法で組み合わせることができるため、SQL の重要なスキルです。 ## 構文 ``` SELECT column1, column2, ..., columnn FROM table1 join_type table2 ON table1.column = table2.column; ``` ## パラメータ *SELECT column1、column2、...、columnN* 結果セットに含める列。JOIN に関係するテーブルのいずれかまたは両方から列を選択できます。 *FROM テーブル 1* JOIN オペレーションの最初の (左) テーブル。 *[JOIN \$1 INNER JOIN \$1 LEFT [OUTER] JOIN \$1 RIGHT [OUTER] JOIN \$1 FULL [OUTER] JOIN] table2: * 実行する JOIN のタイプ。JOIN または INNER JOIN は、両方のテーブルで一致する値を持つ行のみを返します。 LEFT [OUTER] JOIN は、左側のテーブルからすべての行を返し、右側のテーブルから一致する行を返します。 RIGHT [OUTER] JOIN は、右側のテーブルからすべての行を返し、左側のテーブルから一致する行を返します。 FULL [OUTER] JOIN は、一致があるかどうかにかかわらず、両方のテーブルのすべての行を返します。 CROSS JOIN は、2 つのテーブルから行のデカルト積を作成します。 *ON table1.column = table2.column* 2 つのテーブルの行の一致方法を指定する結合条件。結合条件は、1 つ以上の列に基づくことができます。 *WHERE 条件: * 指定された条件に基づいて、結果セットをさらにフィルタリングするために使用できるオプションの句。 ## 例 次の例は、USING 句が含まれている 2 つのテーブル間の結合です。この場合、listid と eventid の各列が結合列として使用されます。結果は 5 行に制限されます。 ``` select listid, listing.sellerid, eventid, listing.dateid, numtickets from listing join sales using (listid, eventid) order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+----------+---------+--------+----------- 1 | 36861 | 7872 | 1850 | 10 4 | 8117 | 4337 | 1970 | 8 5 | 1616 | 8647 | 1963 | 4 5 | 1616 | 8647 | 1963 | 4 6 | 47402 | 8240 | 2053 | 18 ``` # 結合の種類 ## INNER これはデフォルトの結合タイプです。両方のテーブル参照で一致する値を持つ行を返します。 INNER JOIN は、SQL で使用される結合の最も一般的なタイプです。これは、共通の列または列のセットに基づいて複数のテーブルのデータを組み合わせる強力な方法です。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 INNER JOIN table2 ON table1.column = table2.column; ``` 次のクエリは、顧客と注文テーブルの間に一致する customer\$1id 値があるすべての行を返します。結果セットには、customer\$1id、name、order\$1id、および order\$1date 列が含まれます。 ``` SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; ``` 次のクエリは、LISTING テーブルと SALES テーブル間の内部結合です (JOIN キーワードを除く)。ここで、LISTING テーブルの LISTID は 1～5 です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 1、4、および 5 が基準に一致することを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where listing.listid = sales.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次の例は、ON 句を含む内部結合です。この場合、NULL 行は返されません。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは、FROM 句の 2 つのサブクエリを内部結合したものです。このクエリは、イベント (コンサートとショー) の異なるカテゴリのチケットの販売数と売れ残り数を検出します。この FROM 句サブクエリはテーブル**サブクエリです。これらは、複数の列と行を返すことができます。 ``` select catgroup1, sold, unsold from (select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup) as a(catgroup1, sold) join (select catgroup, sum(numtickets)-sum(qtysold) as unsold from category c, event e, sales s, listing l where c.catid = e.catid and e.eventid = s.eventid and s.listid = l.listid group by catgroup) as b(catgroup2, unsold) on a.catgroup1 = b.catgroup2 order by 1; catgroup1 | sold | unsold ----------+--------+-------- Concerts | 195444 |1067199 Shows | 149905 | 817736 ``` ## 左 [外部 ] 左のテーブルリファレンスからすべての値と右のテーブルリファレンスから一致した値を返します。一致しない場合は NULL を追加します。左*外部結合*とも呼ばれます。 左 (1 番目) テーブルのすべての行と右 (2 番目) テーブルの一致する行を返します。右側のテーブルに一致がない場合、結果セットには右側のテーブルの列の NULL 値が含まれます。OUTER キーワードは省略でき、結合は単純に LEFT JOIN として記述できます。LEFT OUTER JOIN の反対は RIGHT OUTER JOIN で、右テーブルのすべての行と左テーブルの一致する行を返します。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column; ``` 次のクエリは、顧客テーブルのすべての行と、注文テーブルの一致する行を返します。顧客が注文がない場合でも、結果セットにはその顧客の情報と order\$1id 列と order\$1date 列の NULL 値が含まれます。 ``` SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id; ``` 次のクエリは左外部結合です。左と右の外部結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したどちらかのテーブルの値を保持します。左のテーブルと右のテーブルは、構文に一覧表示されている最初と 2 番目のテーブルです。NULL 値は、結果セットの「ギャップ」を埋めるために使われます。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTIDs 2 と 3 が売上につながっていないことを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing left outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` ## 右 [ 外部 ] 右側のテーブルリファレンスからすべての値と左側のテーブルリファレンスから一致した値を返します。一致しない場合は NULL を追加します。*右外部結合*とも呼ばれます。 右 (秒) テーブルのすべての行と、左 (最初の) テーブルの一致する行が返されます。左のテーブルに一致がない場合、結果セットには左のテーブルの列の NULL 値が含まれます。OUTER キーワードは省略でき、結合は単に RIGHT JOIN として記述できます。RIGHT OUTER JOIN の反対は LEFT OUTER JOIN で、左側のテーブルのすべての行と右側のテーブルの一致する行を返します。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; ``` 次のクエリは、顧客テーブルのすべての行と、注文テーブルの一致する行を返します。顧客が注文がない場合でも、結果セットにはその顧客の情報と order\$1id 列と order\$1date 列の NULL 値が含まれます。 ``` SELECT orders.order_id, orders.order_date, customers.customer_id, customers.name FROM orders RIGHT OUTER JOIN customers ON orders.customer_id = customers.customer_id; ``` 次のクエリは右外部結合です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 1、4、および 5 が基準に一致することを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing right outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` ## FULL [外部] 両方のリレーションからすべての値を返します。一致しない側に NULL 値を追加します。*完全外部結合*とも呼ばれます。 一致するかどうかにかかわらず、左テーブルと右テーブルの両方からすべての行が返されます。一致する行がない場合、結果セットには、一致する行がないテーブルの列の NULL 値が含まれます。OUTER キーワードは省略でき、結合は単に FULL JOIN として記述できます。FULL OUTER JOIN は、LEFT OUTER JOIN や RIGHT OUTER JOIN よりも一般的には使用されませんが、一致するものがない場合でも、両方のテーブルのすべてのデータを表示する必要がある特定のシナリオで役立ちます。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column; ``` 次のクエリは、顧客テーブルと注文テーブルの両方のすべての行を返します。顧客が注文がない場合でも、結果セットにはその顧客の情報と order\$1id 列と order\$1date 列の NULL 値が含まれます。注文に関連付けられた顧客がない場合、結果セットにはその注文が含まれ、customer\$1id 列と名前列の値が NULL になります。 ``` SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id; ``` 次のクエリは完全結合です。完全結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したテーブルの値を保持します。左のテーブルと右のテーブルは、構文に一覧表示されている最初と 2 番目のテーブルです。NULL 値は、結果セットの「ギャップ」を埋めるために使われます。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTIDs 2 と 3 が売上につながっていないことを示しています。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 次のクエリは完全結合です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。セールスがない行 (LISTID 2 および 3) のみが結果に表示されます。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 and (listing.listid IS NULL or sales.listid IS NULL) group by 1 order by 1; listid | price | comm -------+--------+-------- 2 | NULL | NULL 3 | NULL | NULL ``` ## [ 左] セミ 右側と一致するテーブルリファレンスの左側から値を返します。左*半結合*とも呼ばれます。 右 (秒) テーブルに一致する行を持つ左 (最初の) テーブルの行のみを返します。右側のテーブルの列は返されません。左側のテーブルの列のみが返されます。LEFT SEMI JOIN は、2 番目のテーブルからデータを返すことなく、1 つのテーブルで一致する行を別のテーブルで検索する場合に便利です。LEFT SEMI JOIN は、IN 句または EXISTS 句でサブクエリを使用するよりも効率的な方法です。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 LEFT SEMI JOIN table2 ON table1.column = table2.column; ``` 次のクエリは、注文テーブルに少なくとも 1 つの注文がある顧客について、顧客テーブルの customer\$1id 列と名前列のみを返します。結果セットには、注文テーブルの列は含まれません。 ``` SELECT customers.customer_id, customers.name FROM customers LEFT SEMI JOIN orders ON customers.customer_id = orders.customer_id; ``` ## CROSS JOIN 2 つのリレーションのデカルト積を返します。つまり、結果セットには、条件やフィルターを適用せずに、2 つのテーブルの行の可能な組み合わせがすべて含まれます。 CROSS JOIN は、顧客情報と製品情報の組み合わせをすべて表示するレポートを作成する場合など、2 つのテーブルからデータの組み合わせをすべて生成する必要がある場合に役立ちます。CROSS JOIN は、ON 句に結合条件がないため、他の結合タイプ (INNER JOIN、LEFT JOIN など) とは異なります。結合条件は CROSS JOIN には必要ありません。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 CROSS JOIN table2; ``` 次のクエリは、顧客テーブルと製品テーブルからの customer\$1id、customer\$1name、product\$1id、product\$1name の可能なすべての組み合わせを含む結果セットを返します。顧客テーブルに 10 行があり、製品テーブルに 20 行がある場合、CROSS JOIN の結果セットには 10 x 20 = 200 行が含まれます。 ``` SELECT customers.customer_id, customers.name, products.product_id, products.product_name FROM customers CROSS JOIN products; ``` 次のクエリは、LISTING テーブルと SALES テーブルのクロス結合またはデカルト結合で、結果を制限する述語が使用されています。このクエリは、両方のテーブルの LISTID 1、2、3、4、および 5 について、SALES テーブルと LISTING テーブルの LISTID 列の値と一致します。結果は、20 行が基準に一致することを示しています。 ``` select sales.listid as sales_listid, listing.listid as listing_listid from sales cross join listing where sales.listid between 1 and 5 and listing.listid between 1 and 5 order by 1,2; sales_listid | listing_listid -------------+--------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 4 | 1 4 | 2 4 | 3 4 | 4 4 | 5 5 | 1 5 | 1 5 | 2 5 | 2 5 | 3 5 | 3 5 | 4 5 | 4 5 | 5 5 | 5 ``` ## アンチ結合 左のテーブルリファレンスから、右のテーブルリファレンスと一致しない値を返します。左*アンチ結合*とも呼ばれます。 ANTI JOIN は、あるテーブルで一致する行を別のテーブルで検索する場合に便利です。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 LEFT ANTI JOIN table2 ON table1.column = table2.column; ``` 次のクエリは、注文していないすべての顧客を返します。 ``` SELECT customers.customer_id, customers.name FROM customers LEFT ANTI JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_id IS NULL; ``` ## NATURAL 2 つのリレーションの行が、一致する名前を持つすべての列の等価で暗黙的に一致することを指定します。 2 つのテーブル間で同じ名前とデータ型の列が自動的に照合されます。ON 句で結合条件を明示的に指定する必要はありません。2 つのテーブル間の一致するすべての列を結果セットに結合します。 NATURAL JOIN は、結合するテーブルに同じ名前とデータ型の列がある場合に便利です。ただし、一般的には、より明示的な INNER JOIN を使用することをお勧めします。結合条件をより明確でわかりやすいものにする ON 構文。 **構文:** ``` SELECT column1, column2, ..., columnn FROM table1 NATURAL JOIN table2; ``` 次の例は、次の列を持つ `employees`と の `departments`2 つのテーブル間の自然な結合です。 + `employees` テーブル: `employee_id`、`first_name`、`last_name`、 `department_id ` + `departments` テーブル: `department_id`、 `department_name ` 次のクエリは、`department_id`列に基づいて、2 つのテーブル間で一致するすべての行の名、姓、部門名を含む結果セットを返します。 ``` SELECT e.first_name, e.last_name, d.department_name FROM employees e NATURAL JOIN departments d; ``` 次の例は、2 つのテーブル間の自然結合です。この場合、listid、sellerid、eventid、および dateid の各列は、両方のテーブルで同じ名前とデータ型を持つため、結合列として使用されます。結果は 5 行に制限されます。 ``` select listid, sellerid, eventid, dateid, numtickets from listing natural join sales order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+-----------+---------+--------+----------- 113 | 29704 | 4699 | 2075 | 22 115 | 39115 | 3513 | 2062 | 14 116 | 43314 | 8675 | 1910 | 28 118 | 6079 | 1611 | 1862 | 9 163 | 24880 | 8253 | 1888 | 14 ``` # WHERE 句 WHERE 句には、テーブルの結合またはテーブル内の列への述語の適用のいずれかを実行する条件が含まれています。テーブルは、WHERE 句または FROM 句のいずれかの適切な構文を使うことで結合できます。外部結合基準は、FROM 句で指定する必要があります。 ## 構文 ``` [ WHERE condition ] ``` ## condition** 結合条件やテーブル列に関する述語など、ブール値結果に関する検索条件 次の例は有効な join の条件です。 ``` sales.listid=listing.listid sales.listid<>listing.listid ``` 次の例は、テーブル内の列の有効な条件です。 ``` catgroup like 'S%' venueseats between 20000 and 50000 eventname in('Jersey Boys','Spamalot') year=2008 length(catdesc)>25 date_part(month, caldate)=6 ``` 条件は単純にすることも複雑することもできます。複雑な条件の場合、かっこを使って、論理ユニットを分離します。次の例では、結合条件をかっこによって囲みます。 ``` where (category.catid=event.catid) and category.catid in(6,7,8) ``` ## 使用に関する注意事項 WHERE 句でエイリアスを使って、SELECT リスト式を参照することができます。 WHERE 句内の集計関数の結果を制限することはできません。その目的には、HAVING 句を使用してください。 WHERE 句内で制限されている列は、FROM 句内のテーブル参照から生成する必要があります。 ## 例 次のクエリは、SALES テーブルと EVENT テーブルの結合条件、EVENTNAME 列に関する述語、STARTTIME 列に関する 2 つの述語など、複数の WHERE 句制限の組み合わせを使用します。 ``` select eventname, starttime, pricepaid/qtysold as costperticket, qtysold from sales, event where sales.eventid = event.eventid and eventname='Hannah Montana' and date_part(quarter, starttime) in(1,2) and date_part(year, starttime) = 2008 order by 3 desc, 4, 2, 1 limit 10; eventname | starttime | costperticket | qtysold ----------------+---------------------+-------------------+--------- Hannah Montana | 2008-06-07 14:00:00 | 1706.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 1658.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 3 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 4 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 1 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 4 (10 rows) ``` # VALUES 句 VALUES 句は、テーブルを参照することなく、クエリ内で一連の行値を直接提供するために使用されます。 VALUES 句は、次のシナリオで使用できます。 + INSERT INTO ステートメントで VALUES 句を使用して、テーブルに挿入される新しい行の値を指定できます。 + VALUES 句を単独で使用して、テーブルを参照することなく、一時的な結果セットまたはインラインテーブルを作成できます。 + VALUES 句を WHERE、ORDER BY、LIMIT などの他の SQL 句と組み合わせて、結果セットの行をフィルタリング、ソート、または制限できます。 この句は、永続的なテーブルを作成または参照することなく、SQL ステートメントで小さなデータセットを直接挿入、クエリ、または操作する必要がある場合に特に便利です。これにより、各行の列名と対応する値を定義できるため、個別のテーブルを管理するオーバーヘッドなしで、一時的な結果セットを作成したり、データをその場で挿入したりできます。 ## 構文 ``` VALUES ( expression [ , ... ] ) [ table_alias ] ``` ## パラメータ *expression* 値を生成する 1 つ以上の値、演算子、SQL 関数の組み合わせを指定する式。 *table\$1alias* オプションの列名リストで一時名を指定するエイリアス。 ## 例 次の例では、インラインテーブル、2 つの列を持つ一時テーブルのような結果セット、`col1`および を作成します`col2`。結果セットの 1 行には`1`、それぞれ値 `"one"`と が含まれます。クエリの `SELECT * FROM`部分は、この一時結果セットからすべての列と行を取得するだけです。VALUES 句は列名を明示的に指定しないため、列名 (`col1` および `col2`) はデータベースシステムによって自動的に生成されます。 ``` SELECT * FROM VALUES ("one", 1); +----+----+ |col1|col2| +----+----+ | one| 1| +----+----+ ``` カスタム列名を定義する場合は、VALUES 句の後に AS 句を使用して次のように定義できます。 ``` SELECT * FROM (VALUES ("one", 1)) AS my_table (name, id); +------+----+ | name | id | +------+----+ | one | 1 | +------+----+ ``` これにより、デフォルトの `name`および ではなく`id`、列名 `col1`および を含む一時的な結果セットが作成されます`col2`。 # GROUP BY 句 GROUP BY 句は、クエリのグループ化列を特定します。クエリが SUM、AVG、COUNT などの標準関数を使って集計する場合、グループ化列を宣言する必要があります。SELECT 式に集計関数が含まれている場合、集計関数に含まれていない SELECT 式の列はすべて GROUP BY 句に含まれている必要があります。 詳細については、「[AWS Clean Rooms Spark SQL 関数](sql-functions-topic-spark.md)」を参照してください。 ## 構文 ``` GROUP BY group_by_clause [, ...] group_by_clause := { expr | ROLLUP ( expr [, ...] ) | } ``` ## パラメータ** expr** 列または式のリストは、クエリの SELECT リストの非集計式のリストと一致する必要があります。例えば、次のシンプルなクエリを考慮してみます。 ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by listid, eventid order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` このクエリでは、選択されたリストは 2 つの集計式で構成されています。最初の式は SUM 関数を使用し、2 番目の式は COUNT 関数を使用します。残りの 2 つの例 (LISTID と EVENTID) は、グループ化列として宣言する必要があります。 GROUP BY 句の式は、序数を使用することで、SELECT リストを参照することもできます。例えば、前の例は、次のように短縮できます。 ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by 1,2 order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` ROLLUP** 集計拡張機能の ROLLUP を使用すると、1 つのステートメントで複数の GROUP BY 操作を実行できます。集計拡張機能および関連する関数の詳細については、「[集計拡張機能](GROUP_BY_aggregation-extensions.md)」を参照してください。 # 集計拡張機能 AWS Clean Rooms は、1 つのステートメントで複数の GROUP BY オペレーションの作業を実行するための集約拡張機能をサポートしています。 ## GROUPING SETS** 1 つのステートメントで 1 つ以上のグループ化セットを計算します。グループ化セットとは、1 つの GROUP BY 句のセットで、クエリの結果セットをグループ化できる 0 個以上の列のセットです。GROUP BY GROUPING SETS は、異なる列でグループ化された 1 つの結果セットに対して UNION ALL クエリを実行することに相当します。例えば、GROUP BY GROUPING SETS((a), (b)) は、GROUP BY a UNION ALL GROUP BY b と同等です。 次の例では、製品のカテゴリと販売された製品の種類の両方に従ってグループ化された注文テーブルの製品のコストを返します。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows) ``` ## ROLLUP** 前の列が後続の列の親と見なされる階層を前提としています。ROLLUP は、指定された列ごとにデータをグループ化し、グループ化された行に加えて、グループ化列の全レベルの合計を表す追加の小計行を返します。例えば、GROUP BY ROLLUP((a), (b)) を使用すると、b が a のサブセクションであると仮定して、最初に a でグループ化された結果セットを返し、次に b でグループ化された結果セットを返すことができます。また、ROLLUP では、列をグループ化せずに結果セット全体を含む行を返します。 GROUP BY ROLLUP((a), (b)) は、GROUP BY GROUPING SETS((a,b), (a), ()) と同等です。 次の例では、最初にカテゴリ別にグループ化された注文テーブルの製品のコストを返し、次にカテゴリが細分化された製品を返します。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows) ``` ## CUBE** 指定した列ごとにデータをグループ化し、グループ化された行に加えて、グループ化列の全レベルの合計を表す追加の小計行を返します。CUBE は ROLLUP と同じ行を返しますが、ROLLUP の対象とならないグループ列のすべての組み合わせで小計行を追加します。例えば、GROUP BY CUBE ((a), (b)) を使用すると、b が a のサブセクションであると仮定して、最初に a でグループ化された結果セットを返し、次に b でグループ化された結果セット、さらに b のみでグループ化された結果セットを返すことができます。また、CUBE では、列をグループ化せずに結果セット全体を含む行を返します。 GROUP BY CUBE((a), (b)) は GROUP BY GROUPING SETS((a, b), (a), (b), ()) と同等です。 次の例では、最初にカテゴリ別にグループ化された注文テーブルの製品のコストを返し、次にカテゴリが細分化された製品を返します。前述の ROLLUP の例とは異なり、このステートメントはグループ化列のすべての組み合わせの結果を返します。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows) ``` # HAVING 句 HAVING 句は、クエリが返す中間グループ結果セットに条件を適用します。 ## 構文 ``` [ HAVING condition ] ``` 例えば、SUM 関数の結果を制限できます。 ``` having sum(pricepaid) >10000 ``` HAVING 条件は、すべての WHERE 句条件が適用され、GROUP BY オペレーションが完了してから適用されます。 条件自体は、WHERE 句の条件と同じ形式になります。 ## 使用に関する注意事項 + HAVING 句条件内で参照される列は、グループ化列または集計関数の結果を参照する列のいずれかでなければなりません。 + HAVING 句では、以下の項目を指定することはできません。 + SELECT リスト項目を参照する序数。序数が使用できるのは、GROUP BY 句または ORDER BY 句だけです。 ## 例 次のクエリは、すべてのイベントに対するチケットの合計販売を名前別に計算し、販売合計が 800,000 ドルに達しなかったイベントを削除します。HAVING 条件は、SELECT リスト内の集計関数の結果に適用されます。`sum(pricepaid)`) ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(pricepaid) > 800000 order by 2 desc, 1; eventname | sum ------------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 (6 rows) ``` 次のクエリは、同じような結果セットを計算します。ただしこの場合、SELECT リスト `sum(qtysold)` で指定されていない集計に対して HAVING 条件が適用されます。2,000 枚を超えるチケットを販売しなかったイベントは、最終結果から削除されます。 ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(qtysold) >2000 order by 2 desc, 1; eventname | sum ------------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 Chicago | 790993.00 Spamalot | 714307.00 (8 rows) ``` # セット演算子 *セット演算子*は、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 式の順番を逆にすると、クエリは行を返しません。 # ORDER BY 句 ORDER BY 句は、クエリの結果セットをソートします。 **注記** 最も外側の ORDER BY 式には、SELECT リストにある列だけが含まれている必要があります。 **Topics** + [構文](#ORDER_BY_clause-synopsis) + [パラメータ](#ORDER_BY_clause-parameters) + [使用に関する注意事項](#ORDER_BY_usage_notes) + [ORDER BY の例](Examples_with_ORDER_BY.md) ## 構文 ``` [ ORDER BY expression [ ASC | DESC ] ] [ NULLS FIRST | NULLS LAST ] [ LIMIT { count | ALL } ] [ OFFSET start ] ``` ## パラメータ *expression* クエリ結果のソート順序を定義する式。SELECT リストの 1 つ以上の列から構成されています。結果は、バイナリ UTF-8 順序付けに基づいて返されます。以下を指定することもできます。 + SELECT リストエントリの位置 (SELECT リストが存在しない場合は、テーブルの列の位置) を表す序数 + SELECT リストエントリを定義するエイリアス ORDER BY 句に複数の式が含まれる場合、結果セットは、最初の式に従ってソートされ、次の最初の式の値と一致する値を持つ行に 2 番目の式が適用されます。以降同様の処理が行われます。 ASC \$1 DESC 次のように、式のソート順を定義するオプション: + ASC: 昇順 (数値の場合は低から高、文字列の場合は「A」から「Z」など) オプションを指定しない場合、データはデフォルトでは昇順にソートされます。 + DESC: 降順 (数値の場合は高から低、文字列の場合は「Z」から「A」)。 NULLS FIRST \$1 NULLS LAST NULL 値を NULL 以外の値より先に順序付けするか、NULL 以外の値の後に順序付けするかを指定するオプション。デフォルトでは、NULL 値は昇順ではソートされて最後にランク付けされ、降順ではソートされて最初にランク付けされます。 LIMIT number** \$1 ALL クエリが返すソート済みの行数を制御するオプション。LIMIT 数は正の整数でなければなりません。最大値は `2147483647` です。 LIMIT 0 は行を返しません。この構文は、(行を表示せずに) クエリが実行されているかを確認したり、テーブルから列リストを返すためのテスト目的で使用できます。列リストを返すために LIMIT 0 を使用した場合、ORDER BY 句は重複です。デフォルトは LIMIT ALL です。 OFFSET start** 行を返す前に、start** の前の行数をスキップするよう指定するオプション。OFFSET 数は正の整数でなければなりません。最大値は `2147483647` です。LIMIT オプションを合わせて使用すると、OFFSET 行は、返される LIMIT 行のカウントを開始する前にスキップされます。LIMIT オプションを使用しない場合、結果セット内の行の数が、スキップされる行の数だけ少なくなります。OFFSET 句によってスキップされた行もスキャンされる必要はあるため、大きい OFFSET 値を使用することは一般的に非効率的です。 ## 使用に関する注意事項 ORDER BY 句を使用すると、次の動作が予想されます。 + ヌル値は他のすべての値よりも「高い」と見なされます。デフォルトの昇順のソートでは、NULL 値は最後に表示されます。この動作を変更するには、NULLS FIRST オプションを使用します。 + クエリに ORDER BY 句が含まれていない場合、結果行が返す行の順番は予想不能です。同じクエリを 2 回実行した場合に、結果セットが返される順番が異なることがあります。 + LIMIT オプションと OFFSET オプションは、ORDER BY 句なしに使用できます。ただし、整合性のある行セットを返すには、これらのオプションを ORDER BY と組み合わせて使用してください。 + のような並列システムでは AWS Clean Rooms、ORDER BY が一意の順序を生成しない場合、行の順序は非決定的です。つまり、ORDER BY 式が重複した値を生成する場合、それらの行の戻り順序は他のシステムや の実行ごとに異なる場合があります AWS Clean Rooms 。 + AWS Clean Rooms は、ORDER BY 句の文字列リテラルをサポートしていません。 # ORDER BY の例 CATEGORY テーブルの 11 の行をすべて、2 番目の列 (CATGROUP) でソートして返します。CATGROUP の値が同じ結果の場合、CATDESC 列の値は文字列の長さによってソートされます。次に CATID 列と CATNAME 列でソートされます。 ``` select * from category order by 2, 1, 3; catid | catgroup | catname | catdesc -------+----------+-----------+---------------------------------------- 10 | Concerts | Jazz | All jazz singers and bands 9 | Concerts | Pop | All rock and pop music concerts 11 | Concerts | Classical | All symphony, concerto, and choir conce 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 5 | Sports | MLS | Major League Soccer 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association (11 rows) ``` SALES テーブルの選択した列を、QTYSOLD 値の高い順にソートして返します。結果を上位の 10 行に制限します。 ``` select salesid, qtysold, pricepaid, commission, saletime from sales order by qtysold, pricepaid, commission, salesid, saletime desc salesid | qtysold | pricepaid | commission | saletime ---------+---------+-----------+------------+--------------------- 15401 | 8 | 272.00 | 40.80 | 2008-03-18 06:54:56 61683 | 8 | 296.00 | 44.40 | 2008-11-26 04:00:23 90528 | 8 | 328.00 | 49.20 | 2008-06-11 02:38:09 74549 | 8 | 336.00 | 50.40 | 2008-01-19 12:01:21 130232 | 8 | 352.00 | 52.80 | 2008-05-02 05:52:31 55243 | 8 | 384.00 | 57.60 | 2008-07-12 02:19:53 16004 | 8 | 440.00 | 66.00 | 2008-11-04 07:22:31 489 | 8 | 496.00 | 74.40 | 2008-08-03 05:48:55 4197 | 8 | 512.00 | 76.80 | 2008-03-23 11:35:33 16929 | 8 | 568.00 | 85.20 | 2008-12-19 02:59:33 ``` LIMIT 0 構文を使用することで、列リストを返し、行を返しません。 ``` select * from venue limit 0; venueid | venuename | venuecity | venuestate | venueseats ---------+-----------+-----------+------------+------------ (0 rows) ``` # サブクエリの例 次の例は、サブクエリが SELECT クエリに適合するさまざまな方法を示しています。サブクエリの使用に関する別の例については、「[例](join-clause.md#Join_examples)」を参照してください。 ## SELECT リストのサブクエリ 次の例には、SELECT リストのサブクエリが含まれています。このサブクエリはスカラー値**であり、1 つの列と 1 つの値のみを返します。外部クエリから返される行の結果ごとに、このサブクエリが繰り返されます。このクエリは、サブクエリが計算した Q1SALES 値を、外部クエリが定義する、2008 年の他の 2 つの四半期 (第 2 と第 3) のセールス値と比較します。 ``` select qtr, sum(pricepaid) as qtrsales, (select sum(pricepaid) from sales join date on sales.dateid=date.dateid where qtr='1' and year=2008) as q1sales from sales join date on sales.dateid=date.dateid where qtr in('2','3') and year=2008 group by qtr order by qtr; qtr | qtrsales | q1sales -------+-------------+------------- 2 | 30560050.00 | 24742065.00 3 | 31170237.00 | 24742065.00 (2 rows) ``` ## WHERE 句のサブクエリ 次の例には、WHERE 句にテーブルサブクエリが含まれます。このサブクエリは複数の行を生成します。この場合、その行には列が 1 つだけ含まれていますが、テーブルサブクエリには他のテーブルと同様、複数の列と行が含まれていることがあります。 このクエリは、最大販売チケット数の観点でトップ 10 の販売会社を検索します。トップ 10 のリストは、チケットカウンターが存在する都市に住んでいるユーザーを削除するサブクエリによって制限されます。このクエリは、メインクエリ内の結合としてサブクエリを作成するなど、さまざまな方法で作成できます。 ``` select firstname, lastname, city, max(qtysold) as maxsold from users join sales on users.userid=sales.sellerid where users.city not in(select venuecity from venue) group by firstname, lastname, city order by maxsold desc, city desc limit 10; firstname | lastname | city | maxsold -----------+-----------+----------------+--------- Noah | Guerrero | Worcester | 8 Isadora | Moss | Winooski | 8 Kieran | Harrison | Westminster | 8 Heidi | Davis | Warwick | 8 Sara | Anthony | Waco | 8 Bree | Buck | Valdez | 8 Evangeline | Sampson | Trenton | 8 Kendall | Keith | Stillwater | 8 Bertha | Bishop | Stevens Point | 8 Patricia | Anderson | South Portland | 8 (10 rows) ``` ## WITH 句のサブクエリ 「[WITH 句](WITH_clause.md)」を参照してください。 # 相関性のあるサブクエリ 次の例の WHERE 句には、相関性のあるサブクエリ**が含まれています。このタイプのサブクエリには、サブクエリの列と他のクエリが生成した列の間に相関性があります。この場合、相関は `where s.listid=l.listid` となります。外部クエリが生成する各行に対してサブクエリが実行され、行が適正か適正でないかが判断されます。 ``` select salesid, listid, sum(pricepaid) from sales s where qtysold= (select max(numtickets) from listing l where s.listid=l.listid) group by 1,2 order by 1,2 limit 5; salesid | listid | sum --------+--------+---------- 27 | 28 | 111.00 81 | 103 | 181.00 142 | 149 | 240.00 146 | 152 | 231.00 194 | 210 | 144.00 (5 rows) ``` ## サポートされていない相関サブクエリのパターン クエリのプランナーは、MPP 環境で実行する相関サブクエリの複数のパターンを最適化するため、「サブクエリ相関解除」と呼ばれるクエリ再生成メソッドを使用します。相関サブクエリのいくつかのタイプ AWS Clean Rooms は、関連付けを解除できず、サポートされないパターンに従います。次の相関参照を含んでいるクエリがエラーを返します。 + クエリブロックをスキップする相関参照 (「スキップレベル相関参照」とも呼ばれています) 例えば、次のクエリでは、相関参照とスキップされるブロックを含むブロックは、NOT EXISTS 述語によって接続されます。 ``` select event.eventname from event where not exists (select * from listing where not exists (select * from sales where event.eventid=sales.eventid)); ``` このケースでスキップされたブロックは、LISTING テーブルに対するサブクエリです。相関参照は、EVENT テーブルと SALES テーブルを関係付けます。 + 外部クエリで ON 句の一部であるサブクエリからの相関参照: ``` select * from category left join event on category.catid=event.catid and eventid = (select max(eventid) from sales where sales.eventid=event.eventid); ``` ON 句には、サブクエリの SALES から外部クエリの EVENT への相関参照が含まれています。 + AWS Clean Rooms システムテーブルへの Null センシティブな相関参照。例えば、次のようになります。 ``` select attrelid from my_locks sl, my_attribute where sl.table_id=my_attribute.attrelid and 1 not in (select 1 from my_opclass where sl.lock_owner = opcowner); ``` + ウィンドウ関数を含んでいるサブクエリ内からの相関参照。 ``` select listid, qtysold from sales s where qtysold not in (select sum(numtickets) over() from listing l where s.listid=l.listid); ``` + 相関サブクエリの結果に対する、GROUP BY 列の参照。次に例を示します。 ``` select listing.listid, (select count (sales.listid) from sales where sales.listid=listing.listid) as list from listing group by list, listing.listid; ``` + 集計関数と GROUP BY 句のあり、IN 述語によって外部クエリに接続されているサブクエリからの相関参照。(この制限は、MIN と MAX 集計関数には適用されません。) 例： ``` select * from listing where listid in (select sum(qtysold) from sales where numtickets>4 group by salesid); ```