CODEWITHSUNDEEP
google-bigquery
user2992225
user2992225

Reputation: 247

BigQuery Global Alias of Wildcard for Self Join

Often I need to join a table to itself, and based on the match predicate, print out all the columns from both tables. e.g

SELECT t1.*, t2.* FROM `t` t1 JOIN `t` t2 USING(Id) WHERE xxxx

Issue with this is that you get column name collisions since they are the same source table / cols in each.

Is there a way to avoid this but still use a '*'? I checked the doco and cant see any option in standardSQL

Ideally Id just want a prefix to be applied to the wildcard projections, rather then hand alias each col individually / manually, as a lot of our tables are really wide, and change, and hand cranking is just laborious

e.g Id like to be able to do something like this to prefix each of T2's columns with the string "t2_"

SELECT t1.*, t2.* PREFIX("t2_") FROM `t` t1 JOIN `t` t2 USING(Id) WHERE xxxx

Currently there is likely not a solution as clean as this, unless I missed it in the doco (hope so!), if there are any interim SQL solution using other methods please let me know!

Thanks!

Upvotes: 0

Views: 310

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below approach is a little controversial as it points back to features in BigQuery Legacy SQL - but why not to use them when it is handy :o)

So, below is for BigQuery Legacy SQL and you can use it to get result that you need. In case if you will need features of Standard SQL to further process that result - you can just simply preserve it in the temp table and than use for further processing

Meantime, below code does not require you to specify columns and result is flattened 

#legacySQL
SELECT *
FROM [project:dataset.table] t1
JOIN [project:dataset.table] t2
ON t1.Id = t2.Id  
WHERE xxxx

The trick here is that all columns from table aliased as t1 - will be prefixed with t1_ like t1_col1, t1_col2 and so on ; and all column from t2 will be prefixed with t2_ like t2_col1, t2_col2 and so on - so no column name collisions

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33745

Just select t1 and t2 instead:

SELECT t1, t2 FROM `t` t1 JOIN `t` t2 USING(Id) WHERE xxxx

Upvotes: 1

Related Questions