テーブル内データのdiff（テーブルの一致判定）

2020-04-24追記
2021-08-23URL修正
より深く考察されている記事を見つけました。
BigQuery テーブル同士の一致判定 | Queuery

2025-01-22追記
BigQueryだと、行全体のハッシュ値をとって全行のBIT_XOR()をとって比較するというスマートな方法もあるようです。
BigQuery テーブル比較で使っているクエリ集
また、いつのまにかBigQueryでEXCEPT ALLが使えるようになっていたので追記しました。
ドキュメントに記載されていただけで実際には使えるようにはなっていませんでした、、、😥
（Spannerでは使えたのでドキュメントのミスかリリースされてないだけか）

---

集合演算可能な２つのテーブル（カラムの数と型が同じテーブル）の差異を取得する。

例えば、対象テーブル（foo）の、ある時点のデータを別テーブル（foo_backup）として保存しておき、その内容の差異を取得する。

方法１

(
    select * foo
    union all
    select * foo_backup
)
except all
(
    select * foo
    intersect all
    select * foo_backup
)

方法２

(
    select * foo
    except all
    select * foo_backup
)
union all
(
    select * foo_backup
    except all
    select * foo
)

• 方法２の方が対称的で覚えやすいかも
• allを忘れると重複行が1行になってしまうので注意

ベン図を描かないと理解しにくい、、、。

2020.7.14追記

BigQueryでは EXCEPT ALL が使えないので、代わりに EXCEPT DISTINCT を使う。
ただし当然、2つのテーブル間で同値行の行数が異なる場合でも、1行として扱われてしまうので差異がないことになってしまう。
2025-01-22追記
いつのまにかEXCEPT ALLが使えるようになっていました。
ドキュメントに記載されているだけで実際には使えませんでした。
Query syntax  |  BigQuery  |  Google Cloud