INNER JOIN

INNER JOIN is the workhorse of SQL: line two tables up on a matching rule — almost always "foreign key equals primary key" — and get one combined row for every match. It is the operation you traced by hand on the last page, written out as syntax, and it will carry the large majority of every query you write from here to the end of the book.

The footnote flip from the previous page is exactly what you are about to declare, so no new analogy is needed — this page is that flip, written down. By the end of it you will have written your first two-table query, learned why column names suddenly need their table's name in front, and picked up the alias habit that makes professional SQL readable.

The Anatomy of a JOIN

Start with the question from last page, now answered properly: the names and seats for screening 88. Two tables hold the pieces — bookings has the seats and the pointers, customers has the names. Here is the query, every name written out in full:

SELECT customers.name, bookings.seat
FROM bookings
JOIN customers ON bookings.customer_id = customers.customer_id
WHERE bookings.screening_id = 88;

Read it aloud, slowly: from the bookings table, join in the customers table, matching where the booking's customer_id equals the customer's customer_id; keep the rows for screening 88; show each customer's name and each booking's seat. Every part has a job. FROM names the starting table. JOIN names the table being brought in. ON states the matching rule — the test that decides which booking row lines up with which customer row.

Now read it a second time, the way a database person hears it: the ON clause is the sentence "where the pointer points". bookings.customer_id is the pointer you planted in Chapter 2; customers.customer_id is the primary key it points at. The JOIN follows that pointer for every booking row at once, and the result is the readable table Lora wanted:

Table Names in Front

You may have noticed the new spelling: customers.name instead of plain name. The reason is simple honesty about ambiguity. With two tables in play, some column names exist in both — customer_id lives in customers and in bookings. Write a bare customer_id and the database has no idea which one you mean, so once a query touches two tables, you qualify: table name, a dot, column name.

Writing bookings.customer_id forty times a day gets old, and professionals shorten it with an alias — a nickname for a table, declared right where the table is named. Write FROM bookings b and, for the rest of the query, b means bookings. Here is the same query the way you will see it in the wild:

SELECT c.name, b.seat
FROM bookings b
JOIN customers c ON b.customer_id = c.customer_id
WHERE b.screening_id = 88;

Same query, same answer, half the ink. The nicknames are conventional — first letter of the table, or a short stem like scr — and they are declared once, next to the table they shorten. From this page on, the book writes joins this way, because every multi-table query you meet at a real job will.

What Does "Inner" Mean?

The word inner is a precise promise: only rows that find a match on both sides appear in the answer. Marco, a customer who signed up last month and has never booked anything, has no row in bookings that points at him — so a join of customers and bookings simply does not contain him. He isn't dropped by an error; there was never a matched pair to show.

With a foreign key pointing at a primary key, the asymmetry is predictable. Every booking must point at a real customer — the database enforced that in Chapter 2 — so the "many" side always finds its match. It is the "one" side that can sit out: customers nobody booked for, films nobody scheduled. For this page that is a neutral fact about what inner keeps. The next page turns it into a choice.

Match Rules Are Just Conditions

One honest widening before the quiz. The ON clause is not magic reserved for keys — it takes any true-or-false test, exactly like WHERE does, and "foreign key equals primary key" is simply the test you will want the overwhelming majority of the time. It is the common case, not a law, and the door stays open for the rare day you need something stranger.

And does it matter which table you write first? For the rows an INNER JOIN returns — no; bookings JOIN customers and customers JOIN bookings contain the same matches. For the human reading your query — yes. Lead with the story's subject: a query about bookings starts FROM bookings, and the reader's eye thanks you.