Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
963 B
1
Indexable
Never
WITH 
  table1 AS (
    SELECT * FROM `your_project.your_dataset.table1`
  ),
  table2 AS (
    SELECT * FROM `your_project.your_dataset.table2`
  ),
  combined AS (
    SELECT * FROM table1
    FULL OUTER JOIN table2 ON table1.primary_key = table2.primary_key
  )
SELECT 
  combined.primary_key, 
  'Only in Table 1' AS source_table, 
  combined.*
FROM combined
WHERE 
  combined.primary_key IS NOT NULL AND
  table2.primary_key IS NULL

UNION ALL

SELECT 
  combined.primary_key, 
  'Only in Table 2' AS source_table, 
  combined.*
FROM combined
WHERE 
  combined.primary_key IS NOT NULL AND
  table1.primary_key IS NULL

UNION ALL

SELECT 
  combined.primary_key, 
  'Different values' AS source_table, 
  combined.*
FROM combined
WHERE 
  combined.primary_key IS NOT NULL AND
  table1.primary_key IS NOT NULL AND
  table2.primary_key IS NOT NULL AND
  NOT table1.column1 = table2.column1 -- Replace column1 with the column you want to compare for differences