How to implement MINUS operator in Google Big Query

Question

I am trying to implement MINUS operation in Google Big Query but looks like there is no documentation in Query Reference. Can somebody share your thoughts on this. I have done it in regular SQL in the past but not sure if Google is offering it in Big Query. Your inputs are appreciated. Thank you.

Answer 1

Just adding an update here since this post still comes up in Google Search. BigQuery now supports the EXCEPT set operator.

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#except

select * from t1
EXCEPT DISTINCT
select * from t2;

Answer 2

If BigQuery does not offer minus or except, you can do the same thing with not exists:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t2.col1 = t1.col1 and t2.col2 = t1.col2 . . .
                 );

This works correctly for non-NULL values. For NULL values, you need a bit more effort. And, this can also be written as a left join:

select t1.*
from table1 t1 left join
     table2 t2
     on t2.col1 = t1.col1 and t2.col2 = t1.col2
where t2.col1 is null;

One of these should be acceptable to bigquery.