Combining tables with JOIN

Real databases split data across tables: temperature_readings holds raw measurements keyed by station_id, while weather_stations holds the metadata — name, lat, lon. A JOIN stitches them back together on the shared key, one combined row per match.

The code

SELECT s.name, s.lat, s.lon, r.temp_c
FROM weather_stations AS s
JOIN temperature_readings AS r
  ON s.station_id = r.station_id
ORDER BY r.temp_c DESC
LIMIT 5;

Reading it piece by piece:

1. AS s / AS r — short aliases so you write s.name instead of weather_stations.name.
2. ON s.station_id = r.station_id — the match rule: a row pair survives only when the two IDs agree.
3. Plain JOIN means INNER JOIN — only matches. A reading whose station_id is missing from the stations table silently drops out (which is also how joins audit broken keys).

In practice

Facts in one table, context in another — a data engineer joins them to build the dataset analysts actually query. Sensor readings + station metadata, orders + customers, events + users: same pattern everywhere. Most production queries you'll write are just JOIN + WHERE + GROUP BY composed together.

Further reading

SQL INNER JOIN, animated with GIFs — Data School

Try it

Practice question: using these sample tables, write a query that lists name, lat, lon, and temp_c for all readings above 30°C, sorted by temperature descending.

SELECT s.name, s.lat, s.lon, r.temp_c
FROM weather_stations AS s
JOIN temperature_readings AS r
  ON s.station_id = r.station_id
WHERE r.temp_c > 30
ORDER BY r.temp_c DESC;