本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 設定運算子 *集合運算子*用於比較和合併兩個個別查詢表達式的結果。 AWS Clean Rooms Spark SQL 支援下表所列的下列一組運算子。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/clean-rooms/latest/sql-reference/UNION.html) 例如，如果您想知道哪些網站使用者同時是買方和賣家，但其使用者名稱儲存在不同的資料欄或資料表中，您可以找出這兩種類型使用者的*交集*。如果您想知道哪些網站使用者是買方，但不是賣家，您可以使用 EXCEPT 運算子找出兩份使用者清單之間的*差異*。如果您想要建構所有使用者的清單，但不考慮角色，您可以使用 UNION 運算子。 **注意** ORDER BY、LIMIT、SELECT TOP 和 OFFSET 子句不能用於 UNION、UNION ALL、INTERSECT 和 EXCEPT 集運算子合併的查詢表達式。 **Topics** + [語法](#UNION-synopsis) + [Parameters](#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 } [...] } ``` ## Parameters *subquery1、subquery2* 查詢表達式，以其選取清單的形式對應至 UNION、UNION ALL、INTERSECT、INTERSECT ALL、EXCEPT 或 EXCEPT ALL 運算子後面的第二個查詢表達式。兩個表達式必須包含採用相容資料類型的相同輸出資料欄數，否則就無法比較和合併這兩個結果集。設定操作不允許在不同資料類型類別之間進行隱含轉換。如需詳細資訊，請參閱[類型相容性與轉換](s_Type_conversion.md)。 您可以建構包含無限查詢表達式數目的查詢，並將它們與 UNION、INTERSECT 和 EXCEPT 運算子的任意組合連結。例如，假設資料表 T1、T2 和 T3 包含相容的資料欄集，則以下查詢結構有效： ``` select * from t1 union select * from t2 except select * from t3 ``` UNION 【全部 \$1 DISTINCT】 此集合操作會從兩個查詢表達式傳回資料列，無論資料列衍生自其中一個或兩個表達式。 交集 【全部 \$1 不同】 此集合操作會傳回衍生自兩個查詢表達式的資料列。未由兩個表達式傳回的資料列則會遭到捨棄。 EXCEPT 【全部 \$1 DISTINCT】 此集合操作會傳回衍生自兩個查詢表達式之一的資料列。若要限定結果，資料列必須存在第一個結果資料表中，但不能存在第二個資料表中。 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) ``` ## 使用須知 + 集合操作查詢的結果中傳回的資料欄名稱，是來自第一個查詢表達式的資料表中的資料欄名稱 (或別名)。這些資料欄名稱可能會造成誤導，因為資料欄中的值是從任一邊集合運算子的資料表衍生，所以建議您為結果集提供有意義的別名。 + 當集合運算子查詢傳回小數結果時，對應的結果資料欄就會提升，以傳回相同的精確度和小數位數。例如，在以下查詢中，T1.REVENUE 是 DECIMAL(10,2) 資料欄，而 T2.REVENUE 是 DECIMAL(8,4) 資料欄，小數結果會提升為 DECIMAL(12,4)： ``` select t1.revenue union select t2.revenue; ``` 小數位數為 `4`，因為這是兩個資料欄的小數位數上限。精確度為 `12`，因為 T1.REVENUE 要求小數點左邊有 8 位數 (12 - 4 = 8)。此類型提升可確保 UNION 兩邊的所有值都能納入結果中。若是 64 位元值，最高結果精確度為 19，而結果小數位數上限為 18。若是 128 位元值，最高結果精確度為 38，而結果小數位數上限為 37。 如果產生的資料類型超過 AWS Clean Rooms 精確度和比例限制，查詢會傳回錯誤。 + 在集合操作中，若每個對應資料欄配對的這兩個資料值為*等於*或*兩者皆為 NULL*，則這兩個資料列會視為相同。例如，若資料表 T1 和 T2 都包含一個資料欄和一個資料列，而該資料列在兩個資料表中都是 NULL，則對這些資料表執行 INTERSECT 操作就會傳回該資料列。 # 範例 UNION 查詢 在下列 UNION 查詢中，SALES 資料表中的資料列會與 LISTING 資料表中的資料列合併。會從每個資料表選取三個相容的資料欄；在此情況下，對應的資料欄會有相同的名稱和資料類型。 ``` 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" (表示買方)，來自第二個查詢表達式的資料列識別為 "S" (表示賣家)。 查詢會識別價值 10,000 USD 以上的票券交易買方和賣家。UNION 運算子任一邊的兩個查詢表達式之間唯一的差異，就是 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。LISTING 和 EVENT 資料表中每個資料列的活動 ID 都是唯一的，但 SALES 資料表中相同的活動和清單 ID 組合可能會有多筆銷售。 結果集中的第三個資料欄會識別資料列的來源。若是來自 SALES 資料表，則會在 SALESROW 資料欄中標示為「Yes (是)」 (SALESROW 是 SALES.LISTID 的別名)。若資料列來自 LISTING 資料表，則會在 SALESROW 資料欄中標示為「No (否)」。 在此情況下，結果集會包含活動 7787、清單 500 的三個銷售資料列。換句話說，此清單與活動組合發生了三筆不同的交易。另外兩份清單 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 關鍵字，結果只會保留其中一項銷售交易。 ``` 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。LISTING 和 EVENT 資料表中每個資料列的活動 ID 都是唯一的，但 SALES 資料表中相同的活動和清單 ID 組合可能會有多筆銷售。 結果集中的第三個資料欄會識別資料列的來源。若是來自 SALES 資料表，則會在 SALESROW 資料欄中標示為「Yes (是)」 (SALESROW 是 SALES.LISTID 的別名)。若資料列來自 LISTING 資料表，則會在 SALESROW 資料欄中標示為「No (否)」。 在此情況下，結果集會包含活動 7787、清單 500 的三個銷售資料列。換句話說，此清單與活動組合發生了三筆不同的交易。另外兩份清單 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 關鍵字，結果只會保留其中一項銷售交易。 ``` 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 範例。這兩個範例的唯一差異在於使用的集合運算子，但結果非常不同。只有其中一個資料列相同： ``` 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 ``` 以下查詢會尋找三月份同時於紐約市和洛杉磯的場館舉行的活動 (有售票)。兩個查詢表達式之間唯一的差異就是 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 資料表 (臨時資料表) 包含一個額外的資料列： ``` 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) ``` 傳回兩個資料表之間的差異。換句話說，會傳回 CATEGORY\$1STAGE 資料表而非 CATEGORY 資料表中的資料列： ``` 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 表達式的順序反轉，則查詢不會傳回任何資料列。