聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

Compare two tables in BigQuery

2020-09-17 20:32 浏览: 1171 次 我要评论(0 条) 字号:

As this answer, the best solution for comparing two tables in BigQuery is:

(
  SELECT * FROM table1
  EXCEPT DISTINCT
  SELECT * from table2
)

UNION ALL

(
  SELECT * FROM table2
  EXCEPT DISTINCT
  SELECT * from table1
)

But in my test, two tables with the same rows report difference by using the above snippet. Then I found out that the order of column names may be different, and the order of rows too. Then the better solution should be fixing the order of column names and rows:

(
  (
  SELECT col, col2, col3, col4
  FROM table1
  ORDER BY col1, col2
  )
  EXCEPT DISTINCT
  (
  SELECT col1, col2, col3, col4
  FROM table2
  ORDER BY
  col1, col2
  )
)
UNION ALL
(
  (
  SELECT col, col2, col3, col4
  FROM table2
  ORDER BY col1, col2
  )
  EXCEPT DISTINCT
  (
  SELECT col1, col2, col3, col4
  FROM table1
  ORDER BY
  col1, col2
  )
)

The post Compare two tables in BigQuery first appeared on Robin on Linux.



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复