How to INNER JOIN Three Tables in MySQL

Performing an INNER JOIN across three tables in a MySQL database allows you to combine related data from multiple sources based on matching column values. This article provides a straightforward, step-by-step guide on how to write this query, explains the underlying syntax, and demonstrates a practical example to help you query complex relational databases efficiently.

The Basic Syntax

To join three tables, you chain the INNER JOIN clauses. The first INNER JOIN connects the first and second tables, and the second INNER JOIN connects the second (or first) table to the third table.

Here is the general syntax:

SELECT 
    table1.column_name, 
    table2.column_name, 
    table3.column_name
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column
INNER JOIN table3 ON table2.another_common_column = table3.another_common_column;

Practical Example

Consider a scenario where you have a database containing information about a university. You have three tables:

  1. students: Contains student details.
    • student_id (Primary Key)
    • student_name
  2. courses: Contains course details.
    • course_id (Primary Key)
    • course_name
  3. enrollments: A junction table linking students to their courses.
    • enrollment_id (Primary Key)
    • student_id (Foreign Key referencing students)
    • course_id (Foreign Key referencing courses)

To retrieve a list of students and the names of the courses they are enrolled in, you must join all three tables together.

The SQL Query

SELECT 
    students.student_name, 
    courses.course_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id;

How It Works

  1. The FROM Clause: The query starts by selecting data from the primary table, students.
  2. The First INNER JOIN: It matches rows in the students table with rows in the enrollments table where the student_id is identical.
  3. The Second INNER JOIN: It takes the result of the first join and matches those records with the courses table based on the matching course_id column.
  4. The SELECT Clause: Finally, the query filters the output to display only the student’s name and the course’s name.

Using Table Aliases for Cleaner Code

When querying multiple tables, your SQL statements can quickly become long and difficult to read. You can use table aliases (shortened names) to make your code cleaner and easier to maintain.

Here is the same query using aliases:

SELECT 
    s.student_name, 
    c.course_name
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

In this query, s represents students, e represents enrollments, and c represents courses. This technique reduces repetition and keeps your MySQL queries concise.