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
  )
)

Related Posts