BrevFeed
Sign in

16. INNER JOIN: combining rows across tables

📖 Reading · 8 min
💡 Every code box below is live — edit it and hit Run.

Why joins exist

Recall the relational model from Module 0: relationships are expressed by matching values, not by nesting or pointers. A JOIN is the operation that acts on that match — it combines rows from two tables where a condition you specify holds true. Everything from here through Module 7 builds on this one idea.

INNER JOIN syntax

SELECT c.first_name, c.last_name, r.rental_date
FROM customer c
INNER JOIN rental r ON r.customer_id = c.customer_id
LIMIT 5;

INNER JOIN (often written just JOIN — they're identical, INNER is implied) returns only rows where the ON condition matches on both sides. A customer with zero rentals produces zero rows in this result — a critical fact that motivates the entire next lesson (OUTER JOIN, for when you need unmatched rows too).

Table aliases

c and r above are table aliases — short names standing in for customer and rental for the rest of the query. Not optional in practice once you're joining: without them, SELECT customer_id is ambiguous the instant two joined tables both have a customer_id column, and Postgres will reject the query outright rather than guess:

-- Ambiguous — both tables have customer_id:
-- SELECT customer_id FROM customer JOIN rental ON ...
-- ERROR:  column reference "customer_id" is ambiguous

-- Disambiguated with aliases:
SELECT c.customer_id FROM customer c JOIN rental r ON r.customer_id = c.customer_id LIMIT 3;

This course's convention (and a common industry one): alias to the table's initial letter(s), lowercase, no AS required for table aliases (unlike column aliases, though AS is allowed here too — FROM customer AS c works identically to FROM customer c).

Joining on multiple keys

ON isn't limited to one equality. Two situations call for more than one condition, and it's worth telling them apart:

Composite-key relationships — when a table's primary/foreign key is made of more than one column together, every part must match. pagila's film_actor is a good example of the key itself being composite (its primary key is (actor_id, film_id) together — neither column alone is unique), even though nothing in pagila needs to join against both parts at once. The general shape, joined with AND, looks like this:

-- Hypothetical: if a table stored bookings keyed by (customer_id, visit_date)
-- together rather than a single surrogate id, a join enforcing both parts
-- of that key would read:
-- ON b.customer_id = c.customer_id AND b.visit_date = c.signup_date

Extra filtering baked into the join itself — more common in practice than true composite keys: adding a second AND condition to ON that isn't part of any key at all, to narrow which matching rows join, before WHERE ever runs:

-- Only join in rentals that were also returned same-day, as part of the
-- join condition itself (not a separate WHERE filter):
SELECT c.first_name, r.rental_date, r.return_date
FROM customer c
JOIN rental r
    ON r.customer_id = c.customer_id
    AND r.return_date::date = r.rental_date::date
LIMIT 5;

The distinction matters most with outer joins (next lesson) — putting a condition in ON vs. WHERE changes the result in that case. For inner joins, the two are equivalent — try both in the boxes above.

The USING shorthand

When the join columns have the identical name on both sides (as customer_id does here), USING is a shorter, equivalent spelling:

SELECT c.first_name, r.rental_date
FROM customer c
JOIN rental r USING (customer_id)
LIMIT 5;

One behavioral difference worth knowing: with USING, the joined column appears once in SELECT *, not twice — with ON, SELECT * would include both customer.customer_id and rental.customer_id as separate output columns. Minor, but it explains an otherwise-confusing column count difference if you ever compare the two forms directly.

Chaining joins

Nothing stops you from joining more than two tables — each JOIN adds one more table to the working row set, and later joins can reference columns from any table joined so far:

SELECT c.first_name, c.last_name, f.title, r.rental_date
FROM customer c
JOIN rental r ON r.customer_id = c.customer_id
JOIN inventory i ON i.inventory_id = r.inventory_id
JOIN film f ON f.film_id = i.film_id
LIMIT 5;

This four-table chain is a completely normal, everyday query shape — not an advanced technique. Real schemas are normalized (Module 8) precisely so that answering a real question routinely means walking through several tables like this.

Check yourself

  1. What's the difference in behavior between INNER JOIN and how a plain JOIN keyword behaves in Postgres?
  2. Why does SELECT customer_id become ambiguous once you JOIN customer to rental, and what's the fix?
  3. When can you use USING (col) instead of ON a.col = b.col, and what changes about SELECT * output when you do?
← Previous Next →