INNER JOIN

Introduction

So far, every query you have written has looked at a single table. In real applications, though, data is almost always spread across multiple related tables — a "students" table and a separate "enrollments" table, for example. Joins are how you bring that related data back together in one query.

This lesson covers the most common join of all: the INNER JOIN. It returns only the rows that have a matching partner in both tables involved.

Why We Need Joins

Storing every piece of information in one giant table causes massive duplication. If a student can enroll in multiple courses, repeating that student's full name and details on every enrollment row wastes space and invites inconsistency. Instead, we split the data: one table holds students, another holds enrollments, and a shared column links them together.

A join is a query operation that reconnects those split tables at query time, based on a relationship between their columns.

What is an INNER JOIN?

An INNER JOIN combines rows from two tables and keeps only the rows where a match is found in both tables. If a row in one table has no corresponding row in the other, it is left out of the result entirely.

The Sample Tables

We will use two tables throughout this lesson (and the next several lessons): "students" and "enrollments." Each enrollment row links a student_id back to a row in the students table.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE enrollments (
    id INT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50)
);

INSERT INTO students VALUES
    (1, 'Alex'),
    (2, 'Sam'),
    (3, 'Priya'),
    (4, 'Owen');

INSERT INTO enrollments VALUES
    (101, 1, 'MySQL Basics'),
    (102, 1, 'Web Design'),
    (103, 2, 'MySQL Basics'),
    (104, 3, 'JavaScript 101');

Notice that Owen (id 4) has no rows in the enrollments table at all — he has not enrolled in anything yet. Keep that in mind; it matters a lot for this lesson.

Basic Syntax

An INNER JOIN needs an ON clause that tells MySQL exactly how the two tables relate to each other.

SELECT columns
FROM table1
INNER JOIN table2
    ON table1.column = table2.column;

The ON clause is not optional for INNER JOIN — without it, MySQL would not know which rows in table1 belong with which rows in table2.

Worked Example

Let's find every student together with the courses they are enrolled in, using an INNER JOIN between students and enrollments.

SELECT students.name, enrollments.course_name
FROM students
INNER JOIN enrollments
    ON students.id = enrollments.student_id;

+-------+-----------------+
| name  | course_name     |
+-------+-----------------+
| Alex  | MySQL Basics    |
| Alex  | Web Design      |
| Sam   | MySQL Basics    |
| Priya | JavaScript 101  |
+-------+-----------------+

Notice two things. First, Alex appears twice, once for each course he is enrolled in — that is expected, since the join produces one row per matching pair. Second, and most importantly, Owen does not appear at all, because he has no matching row in enrollments. That is exactly what INNER JOIN is supposed to do: show only students who ARE enrolled in something.

You can also use table aliases to keep the query shorter and easier to read, which becomes especially useful once queries involve more tables.

SELECT s.name, e.course_name
FROM students AS s
INNER JOIN enrollments AS e
    ON s.id = e.student_id;

+-------+-----------------+
| name  | course_name     |
+-------+-----------------+
| Alex  | MySQL Basics    |
| Alex  | Web Design      |
| Sam   | MySQL Basics    |
| Priya | JavaScript 101  |
+-------+-----------------+

The Venn Diagram View

A simple way to picture INNER JOIN is with two overlapping circles, like a Venn diagram. One circle represents all rows in the students table, and the other represents all rows in the enrollments table. INNER JOIN returns only the overlapping middle area — the rows that exist on both sides of the relationship at once.

Anything sitting only in the left circle (a student with no enrollments) or only in the right circle (an enrollment pointing to a student_id that does not exist) is left out of an INNER JOIN result.

Joining More Than Two Tables

INNER JOIN is not limited to two tables — you can chain multiple INNER JOIN clauses to pull in data from several related tables in a single query. Each additional join needs its own ON clause.

SELECT s.name, e.course_name, c.instructor
FROM students AS s
INNER JOIN enrollments AS e ON s.id = e.student_id
INNER JOIN courses AS c ON e.course_name = c.course_name;

We will not run this last example (it references a "courses" table we have not created), but the pattern is worth recognizing early: each INNER JOIN adds one more related table to the result, and every additional join needs its own condition.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

INNER JOIN is the workhorse join for combining related tables — it links students to enrollments through the ON clause, and returns only the rows where a match genuinely exists on both sides.

In this lesson, you learned why joins are necessary, how INNER JOIN filters out unmatched rows, and how to picture it as the overlap in a Venn diagram. Next, you will learn LEFT JOIN, which keeps every row from one table even when there is no match on the other side.