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.
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".
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
col1,col2,col3 a,b,c aa,bb,cc aaa,bbb,ccc aaaa,bbbb,cccc
Next, import this data into table
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 ?
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.
How to get minus difference in BigQuery
In order to get minus difference in BigQuery, you can use set operator
You can use it like
SELECT * FROM test.minus_sample01 EXCEPT DISTINCT SELECT * FROM test.minus_sample02
Result is like below.
You can get rows that only
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.
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 + Bis
A - Bis
There are some other articles about BigQuey.
If you interested in them, please read them.