BigQuery: Symmetric difference (xor) between two sets-CodePudding

BigQuery has UNION, INTERSECT, and EXCEPT [1], but not XOR.

SELECT * FROM [0, 1,2,3] XOR SELECT * FROM [2,3,4]

would return 0 1 4

As 0 and 1 are present in the first select but not second, and 4 is present in the second select, but not first.

I'd like to use it to find discrepancies between two tables, eg find customers that are present in one table, but not other and vice versa.

Any hints how to best do it?

[1]

CodePudding user response：

Few 'workarounds'

Option 1

with table1 as (
  select * from unnest([0, 1,2,3]) num
), table2 as (
  select * from unnest([2,3,4]) num
  )
select * from table1 where not num in (select num from table2)
union all 
select * from table2 where not num in (select num from table1)

Option 2

with table1 as (
  select * from unnest([0,1,2,3]) num
), table2 as (
  select * from unnest([2,3,4]) num
)
select num from (
  select distinct num from table1 union all 
  select distinct num from table2 
)
group by num
having count(*) = 1

in both cases - output is