メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012-12-01)

EXCEPT クエリの例

TICKET データベースの CATEGORY テーブルには、次の 11 行が含まれています。

Copy
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 テーブル (ステージングテーブル) には、1 つの追加行が含まれていると想定します。

Copy
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 つのテーブル間の違いを返します。

Copy
select * from category_stage except select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row)
つまり、CATEGORY テーブル内の行ではなく、CATEGORY_STAGE テーブル内の行が返されるということです。

次の同等のクエリでは、同義語の MINUS を使用します。

Copy
select * from category_stage minus select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row)
SELECT 式の順番を逆にすると、クエリは行を返しません。