範例 EXCEPT 查詢 - AWS Clean Rooms

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

範例 EXCEPT 查詢

在數據庫中的類別表包含以下 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_STAGE 資料表 (臨時資料表) 包含一個額外的資料列:

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_STAGE 資料表而非 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 表達式的順序反轉,則查詢不會傳回任何資料列。