CODEWITHSUNDEEP
sqlgoogle-bigquery
Teja
Teja

Reputation: 13534

How to implement MINUS operator in Google Big Query

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.

Upvotes: 10

Views: 17354

Answers (3)

Matthew Housley
Matthew Housley

Reputation: 176

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;

Upvotes: 11

Endel Guimarães Silva
Endel Guimarães Silva

Reputation: 41

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

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 the problems with nullable fields. Notice: even though this is an old thread, I'm commenting just for sake of completeness if somebody gets to this page in the future.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 4

Related Questions