How to get minus difference in BigQuery

How can I get minus difference in BigQuery ?

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

And also we can use BQ with very cheap price.

Yes. We don't care about price.

Sometimes we want to compare data between 2 tables.

For example, when we change some data procssing program, we must check difference between previous result and modified one.

But How can we get minus difference in BigQuery ?

So today I introduce about "How to get minus difference in BigQuery".

Author

Mid-carieer engineer (AI, system). Good at Python and SQL.

Data

First, we have to prepare data.

This case we try to get table difference. So we prepare 2 tables.

Import CSV data below as table minus_sample01.

col1,col2,col3
a,b,c
aa,bb,cc
aaa,bbb,ccc
aaaa,bbbb,cccc

Next, import this data into table minus_sample02.

col1,col2,col3
a,b,c
aa,bb,cc2
aaa2,bbb,ccc
aaaa,bbbb,cccc

Try "MINUS" to get difference

Then how can we get difference between these tables ?

If Oracle, we can use minus ...

So try it.

SELECT * FROM test.minus_sample01
MINUS
SELECT * FROM test.minus_sample02

Result is below.

Syntax error: Expected end of input but got keyword SELECT at [3:1]

Syntax error, it means SQL sentence was wrong.

So we can't use MINUS in BQ.

How to get minus difference in BigQuery

In order to get minus difference in BigQuery, you can use set operator EXCEPT DISTINCT.

You can use it like MINUS clause.

SELECT * FROM test.minus_sample01
EXCEPT DISTINCT
SELECT * FROM test.minus_sample02

Result is like below.

You can get rows that only minus_sample01 has.

If you want records that only minus_sample02 has, switch fromt sentence for back sentence.

SELECT * FROM test.minus_sample02
EXCEPT DISTINCT
SELECT * FROM test.minus_sample01

Result is like this.

We got unique rows in scond table.

Conclusion

Today I introduced about "How to get minus difference in BigQuery".

In order to get minus difference in BigQuery, we can use method below.

A + B is UNION ALL, A - B is EXCEPT DISTINCT.

There are some other articles about BigQuey.

If you interested in them, please read them.