[presto][SQL] テーブル同士の差分をとりたい時に役立つ ~EXCEPT~

結論

exceptを使おう。

Definition

from Presto 0.230 Documentation / 8.31. SELECT

EXCEPT returns the rows that are in the result set of the first query, but not the second. The following is an example of one of the simplest possible EXCEPT clauses. It selects the values 13 and 42 and combines this result set with a second query that selects the value 13. Since 13 is also in the result set of the second query, it is not included in the final result.:

SELECT * FROM (VALUES 13, 42)
EXCEPT
SELECT 13;

 _col0
-------
   42
(2 rows)

やってみる。

with
  cte_pref_a as(
    select
      id
      , name
    from
      prefecture
  )
  , cte_pref_b as(
    select
      id
      , name
    from
      prefecture
    where
      prefecture.id < 40
  )
  , cte_except_b_from_a as(
    -- a から b を除外する
    select * from cte_pref_a
    except
    select * from cte_pref_b
  )

select
  *
from
  cte_except_b_from_a
order by
  id

id	name
40	福岡県
41	佐賀県
42	長崎県
43	熊本県
44	大分県
45	宮崎県
46	鹿児島県
47	沖縄県

レコード単位ではなくて、カラム単位でいける?

with
  cte_pref_a as(
    select
      id
      , name
    from
      prefecture
  )
  , cte_pref_b as(
    select
      id
      , if( length(name) > 3, substr(name,1,3), name) as name
      -- 4文字以上のやつを、3文字にする
      -- 例) 神奈川県 → 神奈川
    from
      prefecture
  )
  , cte_except_b_from_a as(
    select * from cte_pref_a
    except
    select * from cte_pref_b
  )

select
  *
from
  cte_except_b_from_a
order by
  id

id	name
14	神奈川県
30	和歌山県
46	鹿児島県

他も試したらいけた

with
  cte_pref_a as(
    select
      id
      , name
      , id as id2
    from
      prefecture
  )
  , cte_pref_b as(
    select
      id
      , if( length(name) > 3, substr(name,1,3), name) as name
      -- 4文字以上のやつを、3文字にする
      -- 例) 神奈川県 → 神奈川
      , if( id < 10, id-1, id) as id2
      -- id < 10 なら id - 1 する
    from
      prefecture
  )
  , cte_except_b_from_a as(
    select * from cte_pref_a
    except
    select * from cte_pref_b
  )

select
  *
from
  cte_except_b_from_a
order by
  id

id	name	id2
1	北海道	1
2	青森県	2
3	岩手県	3
4	宮城県	4
5	秋田県	5
6	山形県	6
7	福島県	7
8	茨城県	8
9	栃木県	9
14	神奈川県	14
30	和歌山県	30
46	鹿児島県	46

注意

select * from A
except
select * from B

において、 A - B の構造 になる。
なので

• レコード数は A > B でないと結果が期待とずれることも
• Aに新しいもの、Bに古いものにしないと期待結果とずれることも

あるので注意。