How to implement the MINUS operator in Google Big Query

Question

I am trying to perform a MINUS operation in Google Big Query, but it looks like there is no documentation in the query book. Can someone share their thoughts on this. I have done this in plain SQL in the past, but I'm not sure if Google offers it in Big Query. Your contributions are welcome. Thanks.

Answer 1

If BigQuery does not offer minus or except , you can do the same 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 little more effort. And this can also be written as 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 them should be acceptable for bigquery.

Answer 2

What I usually do is similar to Linoff's answer and always works, regardless of NULL:

 SELECT t1.* FROM table1 t1 LEFT JOIN (SELECT 1 AS aux, * FROM table2 t2) ON t2.col1 = t1.col1 and t2.col2 = t1.col2 WHERE t2.aux IS NULL; 

This solves problems with fields with a zero value. Note: although this is an old thread, I will only comment for the sake of completeness if someone comes to this page in the future.