JOIN

Data gets split across tables so nothing is written twice. Giorgos, who runs the grill, is stored once in stations, not copied onto every grill dish. The shared value, the station name, is what links them.

name             station  price
Bifteki          grill    18
Baklava          pastry   7
Melitzanosalata  salad    16
Loukoumades      pastry   9
Souvlaki         grill    11
Dakos            salad    14

name    lead     section
grill   Giorgos  hot
pastry  Yannick  cold
salad   Sofia    cold
fry     Dan      hot

JOIN ties them together. ON says which columns have to match. It works on any two tables that share a value: here, every regular's favorite station is looked up against stations to find who runs it.

name                 visits  favorite_station  tier
Mrs. Papadaki        42      grill             vip
The Nikolaou family  15      salad             regular
Petros               3       pastry            new
Widow Fotiou         60      grill             vip
Ilias                8       salad             regular
A pair of tourists   1       pastry            new

SELECT c.name, c.favorite_station, s.lead
FROM customers c
JOIN stations s ON c.favorite_station = s.name;

c.name               c.favorite_station  s.lead
Mrs. Papadaki        grill               Giorgos
The Nikolaou family  salad               Sofia
Petros               pastry              Yannick
Widow Fotiou         grill               Giorgos
Ilias                salad               Sofia
A pair of tourists   pastry              Yannick

customers c gives the table a short alias, so c.name and s.name tell SQL which table you mean when both have a column of that name. Skip the alias on a column that only exists in one table if you like, but the prefix never hurts.

A plain JOIN is an INNER one: a row shows up only if the match succeeds on both sides. Every regular's favorite station happens to be a real one, so nobody drops out here, but a row with an unmatched value always would.

LEFT JOIN keeps every row from the first table even when nothing matches, and fills the missing columns with NULL. Starting from staff, that keeps everyone in, even the ones who don't lead a station.

name          role       section  years
Giorgos       cook       hot      12
Yannick       cook       cold     5
Sofia         cook       cold     8
Dan           cook       hot      2
Konstantinos  sommelier  bar      15
Eleni         owner      floor    3
Marina        server     floor    1

SELECT st.name, s.name
FROM staff st
LEFT JOIN stations s ON st.name = s.lead;

st.name       s.name
Giorgos       grill
Yannick       pastry
Sofia         salad
Dan           fry
Konstantinos  NULL
Eleni         NULL
Marina        NULL

A joined result is just a table, so everything from the earlier lessons still applies. You can WHERE it, ORDER BY it, or GROUP BY a column from either side. This averages wine price per line, using a column, section, that only exists in the second table.

name          type     region     price  pairs_with
Assyrtiko     white    Santorini  14     salad
Agiorgitiko   red      Nemea      12     grill
Xinomavro     red      Naoussa    16     grill
Moschofilero  rosé     Mantinia   10     salad
Malagousia    white    Macedonia  11     pastry
Mavrodafni    dessert  Patras     9      pastry

SELECT s.section, AVG(w.price)
FROM wines w
JOIN stations s ON w.pairs_with = s.name
GROUP BY s.section;

s.section  avg
hot        14
cold       11

That is the toolkit. SELECT and WHERE to pick rows and columns, ORDER BY to sort them, the aggregates with GROUP BY and HAVING to summarise, CASE to reshape, and JOIN to read tables together. Almost every order that comes in is a combination of these.