Joins Introduction

Introduction

So far, every query in this course has worked with a single table. But real databases almost always split data across multiple related tables — one for students, one for courses, one for enrollments — to avoid repeating information and to keep data organized. A join is how you bring that related data back together in a single query.

This lesson is a conceptual overview. You will set up two related tables that will be reused across the next several lessons, each of which covers one specific join type in depth.

Why Joins Exist

Imagine storing every student's enrolled course name directly in the students table. If a course name changes, or a student enrolls in multiple courses, this quickly becomes messy and repetitive. Instead, relational databases split related information into separate tables and connect them using a shared key — typically a foreign key that refers to another table's primary key.

A join is the SQL operation that reverses this split at query time: it combines rows from two or more tables into one result, matched together based on that shared key, so you can ask questions that span multiple tables at once.

Setting Up Two Related Tables

Let's create a "students" table and an "enrollments" table that references it. These two tables will be used again in the next several lessons as we cover each join type in detail.

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

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

INSERT INTO students (id, name) VALUES
(1, 'Alex'),
(2, 'Priya'),
(3, 'Sam');

INSERT INTO enrollments (id, student_id, course_name) VALUES
(1, 1, 'Mathematics'),
(2, 1, 'Physics'),
(3, 2, 'Chemistry');

Notice that Sam (id 3) has no matching rows in enrollments, and that student_id in the enrollments table is the shared key that links each enrollment back to a specific student.

How Matching Works

When you join students to enrollments, MySQL looks at every row in students and every row in enrollments, and pairs them together wherever students.id equals enrollments.student_id. Rows that share the same key value are combined side by side into one wider result row.

Notice that Alex appears twice in the matched result — once for each course they are enrolled in — because a join produces one output row for every matching pair. Sam has no enrollment rows to match, which is exactly the kind of case different join types (covered next) handle differently.

A First Join Example

Here is a simple join combining both tables. Don't worry about the exact JOIN syntax yet — the very next lesson covers INNER JOIN in full detail. For now, just notice how the two tables are combined into one result using the shared student_id key.

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

+-------+-------------+
| name  | course_name |
+-------+-------------+
| Alex  | Mathematics |
| Alex  | Physics     |
| Priya | Chemistry   |
+-------+-------------+

Types of Joins You Will Learn Next

MySQL supports several distinct kinds of joins, each with different rules for which unmatched rows are kept or dropped. The next several lessons cover each one using the same students and enrollments tables set up in this lesson.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Joins exist because relational databases spread related data across multiple tables, and a join is how you bring that data back together based on a shared key.

In this lesson, you learned why joins exist, set up a students and enrollments table pair you will reuse throughout the next several lessons, and previewed the specific join types ahead. Next, you will dive into the INNER JOIN in full detail.