SQL INNER JOIN: Combining Matching Rows

Lesson 5.2 · Reading time: ~6 min · Practice tasks ↓

The SQL INNER JOIN combines rows from two tables and returns only the pairs where the join condition is true, that is, rows that have a match in both tables. In this lesson you'll learn the INNER JOIN syntax, why table aliases matter, how to join three tables, and what happens to rows without a match.

In the previous lesson you learned why data is split across tables and how keys connect them. INNER JOIN is the most common way to put the data back together: a city with its country, a payment with its customer, an actor with their films.

What INNER JOIN Returns

INNER JOIN checks the join condition for pairs of rows from the two tables and keeps only the pairs where it is true.

• A row of the first table that has no matching row in the second table is not in the result.
• A row of the second table without a match is not in the result either.
• A row with several matches appears several times, once for each match.

That is why INNER JOIN is often described as the intersection of two tables.

Basic Syntax

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.key_column = table2.key_column;

• INNER JOIN names the second table.
• ON sets the join condition, usually "foreign key = primary key".
• The keyword INNER is optional: JOIN alone means INNER JOIN.

Joining Two Tables

The city table stores country_id, a reference to the country table. To see the country name next to each city:

SELECT city.city, country.country
FROM city
INNER JOIN country ON city.country_id = country.country_id
ORDER BY country.country, city.city;

Result: 600 cities, each with the name of its country.

When both tables have a column with the same name, as country_id here, you must prefix it with the table name; otherwise the database reports an ambiguous column.

Table Aliases

Long table names make joins hard to read. A table alias is a short name defined after the table:

SELECT ci.city, co.country
FROM city AS ci
INNER JOIN country AS co ON ci.country_id = co.country_id
ORDER BY co.country, ci.city;

The result is the same. Once a table has an alias, use the alias everywhere in the query. Choose short but meaningful aliases: ci and co are easier to follow than a and b.

Joining Three Tables

An actor can play in many films, and a film has many actors. Such a many-to-many relationship is stored in a junction table: film_actor holds pairs (actor_id, film_id). To list actors with film titles, join all three tables:

SELECT a.first_name, a.last_name, f.title
FROM actor AS a
INNER JOIN film_actor AS fa ON fa.actor_id = a.actor_id
INNER JOIN film AS f ON f.film_id = fa.film_id
ORDER BY a.last_name, a.first_name, f.title
LIMIT 10;

Result: the first 10 actor–film pairs in alphabetical order of actors.

1. The first join attaches the film_actor rows of each actor.
2. The second join attaches the film of each of those rows.
3. Each JOIN works with the result of the previous one, so you can chain as many tables as needed.

Combining JOIN with WHERE and ORDER BY

A join is followed by the familiar clauses. For example, films with Nick Wahlberg:

SELECT f.title, f.release_year, f.rating
FROM actor AS a
INNER JOIN film_actor AS fa ON fa.actor_id = a.actor_id
INNER JOIN film AS f ON f.film_id = fa.film_id
WHERE a.first_name = 'NICK'
  AND a.last_name = 'WAHLBERG'
ORDER BY f.title;

Result: the titles, release years and ratings of all films with this actor.

Unmatched Rows Disappear

INNER JOIN silently drops rows that have no match. Sakila has 1000 films, but only 958 of them have copies in the inventory table:

SELECT f.title, i.inventory_id, i.store_id
FROM film AS f
INNER JOIN inventory AS i ON i.film_id = f.film_id
ORDER BY f.title
LIMIT 10;

Result: films with their copies; 42 films without copies don't appear at all, and a film with several copies appears several times.

When rows without a match must stay in the result, use LEFT JOIN, which you'll learn in the next lesson.

Key Takeaways:

• INNER JOIN returns only pairs of rows that satisfy the ON condition.
• Rows without a match in the other table are dropped; rows with several matches are repeated.
• JOIN and INNER JOIN are the same.
• Use table aliases and prefix columns that exist in both tables.
• Chain several JOINs to go through junction tables such as film_actor.

Frequently Asked Questions

What is the difference between JOIN and INNER JOIN?

None. JOIN without a type is an INNER JOIN in all major databases. Writing INNER JOIN explicitly makes the intent clearer when a query mixes different join types.

Why does my INNER JOIN return duplicate rows?

Because a row has several matches in the other table. A film with four copies in inventory appears four times after joining film and inventory. This is correct join behavior; aggregate or filter if you need one row per film.

Can I join more than two tables in SQL?

Yes. Add one JOIN ... ON ... per table. Each join connects a new table to the tables already joined.