【Hive】【SQL】差集合 ～ EXCEPT / MINUS etc ～

Hive SQL

■ はじめに

https://dk521123.hatenablog.com/entry/2021/02/11/233633

で記載した警告文
~~~~~~~
unix_timestamp(void) is deprecated. Use current_timestamp instead.
~~~~~~~
に対応し、クエリを修正したのだが、
修正前と修正後の確認で、SQL文「MINUS」を使った。

　そこで、今回は、
SQLでの差集合（集合Aから集合Bの要素を取り去って得られる集合）
について、メモしておく。

使いこなせれば、動作確認時に便利。

目次

【０】注意点
【１】EXCEPT 文
【２】MINUS 文
【３】EXCEPT / MINUS文以外

【０】注意点

* DBによって、サポート状況が異なる

【１】EXCEPT 文

* PostgreSQL / SQL Server etc でサポート

１）サンプル

* PostgreSQL で確認

テストデータ 

CREATE TABLE IF NOT EXISTS "employee_1" (
  "emp_id" INTEGER NOT NULL,
  "emp_name" VARCHAR(50) NOT NULL,
  PRIMARY KEY ("emp_id")
);

CREATE TABLE IF NOT EXISTS "employee_2" (
  "emp_id" INTEGER NOT NULL,
  "emp_name" VARCHAR(50) NOT NULL,
  PRIMARY KEY ("emp_id")
);

INSERT INTO "employee_1" ("emp_id", "emp_name") VALUES
  (1, 'Tom'), -- 共通
  (3, 'Anny'); -- employee_1 のみのデータ

INSERT INTO "employee_2" ("emp_id", "emp_name") VALUES
  (1, 'Tom'), -- 共通
  (2, 'Nick'); -- employee_2 のみのデータ

SQL 

SELECT * FROM employee_1
EXCEPT
SELECT * FROM employee_2
;
-- "3" "Anny" <= employee_2にないデータを返す

SELECT * FROM employee_2
EXCEPT
SELECT * FROM employee_1
;
-- "2" "Nick" <= employee_1にないデータを返す

参考文献
https://qiita.com/satthi/items/16a4b3e6fc57c4d7b97c

【２】MINUS 文

* Oracle etc で使用可能
* Hive は未サポートって言ってるけど実行できたような、、、

https://stackoverflow.com/questions/30760371/minus-query-in-hive/30760532

１）サンプル

SQL 

SELECT * FROM employee_1
MINUS
SELECT * FROM employee_2
;

SELECT * FROM employee_2
MINUS
SELECT * FROM employee_1
;

【３】EXCEPT / MINUS文以外

* EXCEPT / MINUS文どちらもサポートしていないDBの場合、
　「NOT EXISTS + 相関サブクエリ」や「LEFT JOIN + IS NULL」
　で実現させるみたい

https://qiita.com/Hiraku/items/71873bf31e503eb1b4e1

参考文献

https://www.dbsheetclient.jp/blog/?p=729

関連記事

Hive / HiveQL ～ 日時・日付操作編 ～
https://dk521123.hatenablog.com/entry/2021/02/11/233633
SQL ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2023/01/16/000000
SQL ～ テーブル差分を調査することを考える ～
https://dk521123.hatenablog.com/entry/2024/09/22/220455
Snowflake ～ 関係演算 / 集合論 ～
https://dk521123.hatenablog.com/entry/2024/09/12/003814