Code / SQL with Data / Unit 5: Joins, changes and the final project

JOIN: connecting two tables JOIN: दो टेबल जोड़ना

Lesson 11 of 13 · about 16 minutes · INNER JOIN and LEFT JOIN with ON.

⚡ 0 XP

Step 1 of 2

Matching rows with JOIN

The trains table stores station codes like NDLS. The city name lives in the stations table. Why split them? So each city is written only once — if a name changes, you fix one row.

JOIN puts matching rows side by side. ON says how they match:

SELECT trains.name, stations.city FROM trains JOIN stations ON trains.from_station = stations.code;

Short names (aliases for tables) save typing: FROM trains t JOIN stations s ON t.from_station = s.code, then use t.name and s.city. A plain JOIN is an INNER JOIN: it keeps only rows that have a match on both sides.

Example

SELECT t.name, s.name AS station
FROM trains t
JOIN stations s
  ON t.to_station = s.code;

🎯 Challenge: Which city does it start from? +20 XP

Join trains with stations on from_station = code. Show the train name as train, the city as from_city and departs, for all trains.

Try twice to unlock the solution (0/2)

query.sqlCtrl ⏎

Tables (practice database)

Results

Press Run to see your query’s rows here.

Checklist

Press Run to check your code.

Your rows match the expected answer
Uses JOIN … ON

Lesson notes (text version)

1. Matching rows with JOIN

The trains table stores station codes like NDLS. The city name lives in the stations table. Why split them? So each city is written only once — if a name changes, you fix one row.

JOIN puts matching rows side by side. ON says how they match:

SELECT trains.name, stations.city FROM trains JOIN stations ON trains.from_station = stations.code;

                SELECT t.name, s.name AS station
FROM trains t
JOIN stations s
  ON t.to_station = s.code;

Challenge: Which city does it start from?

Join trains with stations on from_station = code. Show the train name as train, the city as from_city and departs, for all trains.

2. LEFT JOIN: keep everything on the left

An INNER JOIN drops rows with no partner. Sometimes the missing partners are exactly what you want — like products that never sold.

LEFT JOIN keeps every row of the left table. If there is no match, the right side’s columns are filled with NULL:

SELECT p.name, s.qty FROM products p LEFT JOIN sales s ON s.product_id = p.id;

Add WHERE s.id IS NULL and only the products with no sale at all remain. That is how shops find dead stock.

                SELECT s.city, t.name
FROM stations s
LEFT JOIN trains t
  ON t.from_station = s.code;

Challenge: Products that never sold

Using products LEFT JOIN sales ON sales.product_id = products.id, show the name and price of products that have no sale at all.