Question: How to join tables using join clause without listing all the fields?
Data
Given two tables, Person and Address:
Person
| name | address_id |
|---|---|
| Alice | 10 |
| Bob | 11 |
| Charlie | 10 |
Address
| id | street | city |
|---|---|---|
| 10 | William Street | NYC |
| 11 | Old Street | London |
Desired result:
I'd like to join them with a record, like so:
| name | address.street | address.city |
|---|---|---|
| Alice | William Street | NYC |
| Bob | Old Street | London |
| Charlie | William Street | NYC |
However, I have many columns in both tables and I don't want to specify them all.
So something a bit like using EXCEPT but with the joined columns becoming nested in an address record:
SELECT * EXCEPT (address_id)
FROM person p
JOIN address a
ON p.address_id = a.id
Is this possible in BigQuery?
CodePudding user response：
Consider below query:
SELECT p.* EXCEPT(address_id), (SELECT AS STRUCT a.* EXCEPT(id)) AS address
FROM Person p JOIN Address a ON p.address_id = a.id;
output:
