INNER JOIN: matching rows across tables

Part of the free LogicWiz SQL & Databases course, Module 5: Joins. The queries and practice run in the app on real Postgres.

A customer calls support: "Where is order 1014?" The orders row says customer_id 8, restaurant_id 7, partner_id 4. Numbers. To read it like a human you need three other tables at once.

You'll learn to

• Join two tables on a key with JOIN … ON
• Use short table aliases and qualify columns
• Chain three or four tables in one query
• Explain why rows with no match disappear

Learn

Why the data is split

Aarav and Meher's food app stores each customer once, in customers. An order does not copy the name and city; it stores only customer_id, a foreign key pointing at the customer.

That saves space and keeps one truth: if Meher changes her city, it changes in one row.

The price is that a question like "which customer placed order 1014?" needs two tables. A join glues them back together: for each order, find the customer row whose customer_id matches.

Your first join

SELECT o.order_id, c.name, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

orders o gives the table a short alias o. ON says which rows belong together. Every order has a customer, so you get all 24 orders, now with names.

What JOIN … ON does

Think of it as: try every pair, keep the pairs where ON is true. Postgres is smarter about it, but the result is the same. A row whose key is NULL or has no partner on the other side matches nothing and is dropped.

Three tables: order, customer, restaurant

SELECT o.order_id, c.name AS customer, r.name AS restaurant, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN restaurants r ON r.restaurant_id = o.restaurant_id
WHERE o.order_id = 1014;

Each JOIN adds one table and one ON. Read it top to bottom: start from orders, attach the customer, then attach the restaurant. Remove the WHERE to see every order.

Two classic slips:

• Unqualified columns. customers and restaurants both have name and city. Writing just city gives column reference "city" is ambiguous. Always write c.city.
• The wrong key. ON r.restaurant_id = o.customer_id runs without any error and returns nonsense pairs. Match a foreign key with the key it points to.

Down to the dish: four tables

SELECT o.order_id, m.name AS item, oi.qty, m.price * oi.qty AS amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN menu_items m ON m.item_id = oi.item_id
WHERE o.order_id = 1003
ORDER BY m.name;

order_items is a junction table: one row per dish in an order. Joining through it turns one order into one row per dish. Keep that in mind; it matters a lot in lesson 4.

JOIN and INNER JOIN are the same thing. When both columns have the same name you can write JOIN customers c USING (customer_id). You may also see the old style FROM orders o, customers c WHERE c.customer_id = o.customer_id. It works, but forget the WHERE and you silently get every pair. Prefer JOIN … ON.

Interviewers start the join round with "what is an inner join?" Give the one-line answer: it returns only the pairs of rows where the join condition is true; rows with no match on either side are left out. Then mention that a NULL key never matches. In exams, an inner join is the relational algebra theta join, and with = it is an equi-join.

In one line

JOIN t ON key = key keeps only matching pairs: rows with no match, or a NULL key, disappear.