Multiple JOINs in One Query

Imagine every table in your database is a piece of one big puzzle. The students table knows who is studying, the courses table knows what is being taught, and the enrollments table knows who is enrolled in what. But on their own, these pieces don’t tell you much. Want to see the whole picture? You gotta connect them—and that’s where multiple JOINs come in.

In real life, data is often organized into related tables to keep things structured and avoid redundancy. For example, in our university database, we have these tables:

• students — info about students.
• enrollments — info about students enrolling in courses.
• courses — info about courses.

If we want a full list of students, their courses, and teachers, we need to join all three tables using JOIN.

Order of JOIN Execution

When you use several JOINs, PostgreSQL processes them left to right. That means it joins the first two tables, then joins the result with the third table, and so on.

Example:

SELECT *
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id;

1. First, the students and enrollments tables are joined on students.id = enrollments.student_id.
2. The result of the first join is then joined with the courses table on enrollments.course_id = courses.id.

The order of execution is especially important when you’re dealing with big tables. A bad JOIN structure can seriously slow things down.

Example: List of Students, Their Courses, and Teachers

Let’s say we have these tables with data:

Table students:

Table courses:

Table enrollments:

Query:

SELECT
    students.name AS student_name,
    courses.name AS course_name,
    courses.teacher AS teacher_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id;

Result:

Filtering in Queries with Multiple JOINs

You can use filtering conditions in JOIN queries to limit the amount of data returned and speed things up. For example, if you only want students who are taking the "Mathematics" course:

SELECT
    students.name AS student_name,
    courses.name AS course_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id
WHERE courses.name = 'Mathematics';

Result:

Optimizing Queries with Multiple JOINs

When you’re working with big tables, query optimization is super important. Here are a few tips:

1. Use indexes

Indexes let PostgreSQL work faster, especially when joining on key fields. Make sure you have indexes on the student_id and course_id columns in the enrollments table.

Example of creating an index:

CREATE INDEX idx_enrollments_student_id ON enrollments(student_id);
CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);

You’ll learn more about indexes in later levels, but I wanted to mention them here. They’re super often used with JOINs.

2. Filter data early

PostgreSQL’s optimizer is usually smart enough to apply WHERE conditions as early as possible (predicate pushdown), so adding filters is a good practice. The formal logical order is FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY, but the planner is allowed to reorder steps for performance. For example:

SELECT
    students.name AS student_name,
    courses.name AS course_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id
WHERE 
    courses.teacher = 'Ivan Petrov';

3. Minimize the number of rows to join

Instead of joining all records from two tables, try filtering them first with subqueries:

SELECT
    students.name AS student_name,
    courses.name AS course_name
FROM 
    (SELECT * FROM students WHERE id IN (1, 2)) sub_students
INNER JOIN enrollments ON sub_students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

You’ll learn more about nested SELECTs literally in the next level :P

Example of a Complex Join: Students, Courses, and Faculties

Let’s say we add another table faculties:

Table faculties:

Table courses updated:

To get a list of students, courses, and faculties, we add one more JOIN:

SELECT
    students.name AS student_name,
    courses.name AS course_name,
    faculties.name AS faculty_name
FROM students
    INNER JOIN enrollments ON students.id = enrollments.student_id
    INNER JOIN courses ON enrollments.course_id = courses.id
    INNER JOIN faculties ON courses.faculty_id = faculties.id;

Result:

SQL queries with multiple JOINs can get tricky, but they let you build powerful reports and get valuable insights. If you optimize and structure them right, they’ll be your go-to tool for working with big databases.