I have two queries that create two separate tables, pulling on several foreign keys. Simplified and using retail food as an example, Table 1 looks like this: Transaction ID City Store Item Description 12320 Boston Joe's Shop 12305 Banana 12321 LA Bob's Market 12306 Apple 12322 NY Suzy's Corner Store 12307 Strawberry Table 2 looks like this: Transaction ID City

Join two SQL/BigQuery tables that do not contain the same columns

I have two queries that create two separate tables, pulling on several foreign keys.

Simplified and using retail food as an example, Table 1 looks like this:

Transaction ID	City	Store	Item	Description
12320	Boston	Joe’s Shop	12305	Banana
12321	LA	Bob’s Market	12306	Apple
12322	NY	Suzy’s Corner Store	12307	Strawberry

Table 2 looks like this:

Transaction ID	City	Item	Description
12323	SF	12308	Pineapple
12324	Houston	12306	Apple
12325	Miami	12307	Strawberry

Essentially, Table 2 contains transactions that do not have a store, and as such are treated differently in our company (and reported on separately). However, I was wondering how can I incorporate both tables to achieve something like this? I am getting stuck since the data pull for each table comes from different places, since in this example, transactions sold without a store get classified completely differently.

Transaction ID	City	Store	Item	Description
12320	Boston	Joe’s Shop	12305	Banana
12321	LA	Bob’s Market	12306	Apple
12322	NY	Suzy’s Corner Store	12307	Strawberry
12323	SF	No Store	12308	Pineapple
12324	Houston	No Store	12307	Apple
12325	Miami	No Store	12307	Strawberry

Thank you in advance for your help!

Answer

In SQL this is called a UNION. You can union two sets if they have the same columns, such as:

select trans_id, city, store, item, description from table1
union
select trans_id, city, 'No Store' as store, item, description from table2

Advertisement

Answer