1. Two tables from one Data set

Create Dataset :
bq mk secondDataset2



Create table :
bq mk secondDataset2.trips_2014
bq mk secondDataset2.trips_2015



Identify the common Key and data:

bq query "select trip_type from secondDataset2.trips_2014 where trip_type is not null group by trip_type limit 100"Waiting on bqjob_r203243b94d6f4f31_0000015ef07291b3_1 ... (0s) Current status: DONE

bq query "select trip_type from secondDataset2.trips_2015 where trip_type is not null group by trip_type limit 100"Waiting on bqjob_r6cb5e46ad13998ba_0000015ef073b4fb_1 ... (0s) Current status: DONE



 Joins:
Query : (default will be inner)
bq query "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance , secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type from secondDataset2.trips_2014 join secondDataset2.trips_2015
on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"



Left Outer Join

Query :
bq query "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from secondDataset2.trips_2014 left outer join secondDataset2.trips_2015 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"



Right and Full outer error:

Error :
Error in query string: Error processing job 'root-stock-181905:bqjob_r68ebef2ce4936cb7_0000015ef0a2129a_1': 3.32 - 0.0: FULL OUTER and RIGHT OUTER joins are only supported
with JOIN EACH
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]




Standard SQL execution:

sureshpuli_gcp@cluster-d443-m:~$ bq query --use_legacy_sql=false "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
> secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
> from secondDataset2.trips_2014 full outer join secondDataset2.trips_2015 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"
Error in query string: Error processing job 'root-stock-181905:bqjob_r755502505dd46c5_0000015ef0fb0e36_1': Unrecognized name: secondDataset2 at [3:77]
sureshpuli_gcp@cluster-d443-m:~$
sureshpuli_gcp@cluster-d443-m:~$ bq query --use_legacy_sql=false "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
> secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
> from secondDataset2.trips_2014 right outer join secondDataset2.trips_2015 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"
Error in query string: Error processing job 'root-stock-181905:bqjob_r623b29e5f728d3a5_0000015ef0fbe5de_1': Unrecognized name: secondDataset2 at [3:78]


Modified Query with each:

Query : (Right Outer)

bq query "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from secondDataset2.trips_2014 right outer join each secondDataset2.trips_2015 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"





Query :(Full Outer)

bq query "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from secondDataset2.trips_2014 full outer join each secondDataset2.trips_2015 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"



  
Inner Join :

Query :
bq query "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from secondDataset2.trips_2014 inner join secondDataset2.trips_2015 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"




Self Join:

bq query "select secondDataset2.trips_2014.trip_type , secondDataset2.trips_2014.trip_distance ,
secondDataset2.trips_2014.fare_amount , secondDataset2.trips_2014.payment_type
from secondDataset2.trips_2014 join secondDataset2.trips_2014 on secondDataset2.trips_2014.trip_type = secondDataset2.trips_2014.trip_type limit 10"

Error:
Error in query string: Error processing job 'root-stock-181905:bqjob_r26b23970c3a8af20_0000015ef0e9c2c5_1': 0.0 - 0.0: Cannot join two tables with the same name. Please give
one table an alias name.



Modified Query:

bq query "select A.trip_type , A.trip_distance ,
A.fare_amount , A.payment_type
from secondDataset2.trips_2014 A join secondDataset2.trips_2014 B on A.trip_type = B.trip_type limit 10"



  


2. Two tables from different Data sets

Join(By default Inner):
Query:
bq query "select Netteza2Bq.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
Netteza2Bq.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from Netteza2Bq.trips_2014 join secondDataset2.trips_2015 on Netteza2Bq.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"


  
Left Outer Join:

Query:

bq query "select Netteza2Bq.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
Netteza2Bq.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from Netteza2Bq.trips_2014 left outer join secondDataset2.trips_2015 on Netteza2Bq.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"




Full and Right Outer Join:

Error:
Error in query string: Error processing job 'root-stock-181905:bqjob_r53d9436b5cc52819_0000015ef0e1b3cb_1': 3.28 - 0.0: FULL OUTER and RIGHT OUTER joins are only supported
with JOIN EACH
[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]

Modified Query with each:

Query : (Right Outer join)

bq query "select Netteza2Bq.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
Netteza2Bq.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from Netteza2Bq.trips_2014 right outer join each secondDataset2.trips_2015 on Netteza2Bq.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"



Query :(Full Outer Join)

bq query "select Netteza2Bq.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
Netteza2Bq.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from Netteza2Bq.trips_2014 full outer join each secondDataset2.trips_2015 on Netteza2Bq.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"




Inner Join:

Query:

bq query "select Netteza2Bq.trips_2014.trip_type , secondDataset2.trips_2015.trip_distance ,
Netteza2Bq.trips_2014.fare_amount , secondDataset2.trips_2015.payment_type
from Netteza2Bq.trips_2014 inner join secondDataset2.trips_2015 on Netteza2Bq.trips_2014.trip_type = secondDataset2.trips_2015.trip_type limit 10"

  


Self Join:

Query:

bq query "select A.trip_type , B.trip_distance ,
A.fare_amount , A.payment_type
from Netteza2Bq.trips_2014 A join secondDataset2.trips_2014 B on A.trip_type = B.trip_type limit 10"