Trying to JOIN columns from two different tables

I have one table (city_data) with the columns ‘year’, ‘city_name’ and ‘avg_temp’. The other table is global_data and has the columns ‘avg_temp’ and ‘year’. I am trying to create a table which has the average temperature of London, the global average temperature for all the years. My SQL code is the following:

SELECT city_data.avg_temp, city_data.year
FROM city_data
JOIN global_data
ON city_data.year = global_data.avg_temp
WHERE city= 'Paris'

I get no error but cannot get the expected result. I think I am not able to understand what I need to put in the ON clause.

Answer

I suppose that the table global_data has one row per year, while city_data has one row per year and city. This means you can simply join the two on the year they have in common:

select year, g.avg_temp as global_average, c.avg_temp as paris_average
from global_data g
join city_data c using (year)
where c.city = 'Paris'
order by year;

If you don’t like to join the two and only then mention Paris, you can also turn this into:

select year, g.avg_temp as global_average, p.avg_temp as paris_average
from global_data g
join (select * from city_data where city = 'Paris') p using (year)
order by year;

As of MySQL 8 you can even use a WITH clause to “prepare” Paris 🙂

with paris as (select * from city_data where city = 'Paris')
select year, g.avg_temp as global_average, paris.avg_temp as paris_average
from global_data g
join paris using (year)
order by year;

Or consider Paris a join criteria:

select g.year, g.avg_temp as global_average, p.avg_temp as paris_average
from global_data g
join city_data p on p.year = g.year and p.city = 'Paris'
order by g.year;

As you see, there are many ways to write the same query and the DBMS would probably come up with the same execution plan for all. So, just pick the query you like best.