include additional columns while using WHERE IN

x89 0
Viewed 40

I have a table that looks like this.

id        name
1         firstName
2         secondName
3         thirdName
4         fourthName

I want to keep all rows where the name is present in either the "Country_name_EN" or "Country_Code" columns from the second table that looks like this:

Country_name_EN  Country_Code        coordinates
firstName       EN                  124
random          secondName          1244
thirdName       DE                  689
FifthName       DE                  457

I want a resulting table that looks like this. I also want to include the coordinates for all rows where the name is found in table2:

id        name                coordinates
1         firstName           124
2         secondName          1244
3         thirdName           689

This code works for the joins but not sure how to include the coordinates into it

select * 
  from `t1` as test
  where test.name in 
  (select test.name
      from `t2` as geonames 
      where geonames.Country_name_EN = test.name or geonames.Country_Code = test.name)
where-in left-join join google-bigquery sql
1 Answers

A WHERE IN clause can always be rewritten as join, which is usually fatser that IN in big databases, only with a JOIN you can access columns from both tables

So you do

SELECT `t1`.id, `t1`.name,`t2`.coordinates
from `t1` JOIN `t2` ON `t1`.name = `t2.Country_name_EN OR  geonames.Country_Code = `t1`.name
nbk0
Related