sql - Comparing two db tables and appending values from one to another

Question

I have two DB tables. The first, all_data, contains around 4m rows with information on user interactions on a site. The second, region, contains a list of 1,800 towns in the UK, with their corresponding county and TV region.

One of the all_data columns references the town where the user lives. I need to check each row of all_data against all the rows of region. If the town matches, I need to append the county and TV region from region to the row in all_data.

I’m relatively new to SQL and I’m having trouble visualizing how this should work. As this is taking place in BigQuery, I need to keep processing costs to a minimum.

question from:https://stackoverflow.com/questions/65844541/comparing-two-db-tables-and-appending-values-from-one-to-another

Answer 1

This sounds like a left join:

select a.*, r.country, r.tv_region
from all_data a left join
     region r
     on a.town = r.town;