how to dynamically join tables in bigquery to avoid duplication of common columns

Question

I have 2 tables with a large number of columns (each has around 700-800 columns, which makes it not feasible to individually write all the column names). Both the tables have a few common rows. I need to dynamically union both the tables such that the common columns don't get repeated and are queried only once in the final table. For example:

TABLE 1:
+---------+--------+------+-------+
|firstname|lastname|upload|product|
+---------+--------+------+-------+
|    alice|       a|   100|apple  | 
|      bob|       b|    23|orange |
+---------+--------+------+-------+

TABLE 2:

+---------+--------+------+-------+
|firstname|lastname|books |active |
+---------+--------+------+-------+
|    alice|       a|   10 |yes    | 
|      bob|       b|    2 |no     |
+---------+--------+------+-------+

FINAL TABLE:

+---------+--------+------+-------+-----+------+
|firstname|lastname|upload|product|books|active|
+---------+--------+------+-------+-----+------+
|    alice|       a|   100|apple  | 10  | yes  |
|      bob|       b|    23|orange | 2   | no   | 
+---------+--------+------+-------+-----+------+

You should write a script to write your SQL. Extract the schema of every tables and only keep what's common between them. — vdolez, Mar 18 '22 at 14:18
please do not change/adjust already answered question! rather post new one! — Mikhail Berlyant, Mar 18 '22 at 20:07

score 1 · Accepted Answer · answered Mar 18 '22 at 14:25

1

Just to give you a direction to look into

select *
from table1
join table2
using(firstname, lastname)

if applied to sample data in your question - output is

answered Mar 18 '22 at 14:25

Mikhail Berlyant

165,386
8
154
230

What should I do if there are multiple common columns? Is there any way to do it without explicitly mentioning the common column names in the query? – Sanjana Mar 18 '22 at 15:19

how to dynamically join tables in bigquery to avoid duplication of common columns

1 Answers1