Join two tables based on relationship defined in third table

Joining Tables Based on a Relationship Defined in a Third Table

Learn how to perform complex SQL joins where the relationship between two primary tables is mediated by a third, linking table. This article covers common scenarios and provides practical SQL examples.

In relational databases, it's common to encounter scenarios where two tables don't have a direct foreign key relationship but are connected through an intermediary table. This often occurs in many-to-many relationships, where the third table (sometimes called a 'junction table' or 'associative table') stores the pairings between records from the other two tables. Understanding how to join these three tables effectively is crucial for retrieving comprehensive data.

Understanding the Many-to-Many Relationship

A many-to-many relationship exists when multiple records in one table can relate to multiple records in another table. For example, a student can enroll in multiple courses, and a course can have multiple students. A direct foreign key relationship between Students and Courses tables isn't sufficient to model this. Instead, a third table, such as Enrollments, is introduced to link them. This Enrollments table would typically contain foreign keys referencing both the Students table and the Courses table.

erDiagram
    STUDENTS ||--o{ ENROLLMENTS : "has enrolled in"
    COURSES ||--o{ ENROLLMENTS : "is taken by"
    
    STUDENTS {
        INT student_id PK
        VARCHAR student_name
    }
    
    COURSES {
        INT course_id PK
        VARCHAR course_name
    }
    
    ENROLLMENTS {
        INT enrollment_id PK
        INT student_id FK
        INT course_id FK
        DATE enrollment_date
    }

Entity-Relationship Diagram for Students, Courses, and Enrollments

Performing the Three-Table Join

To join two tables (e.g., Students and Courses) via a third table (Enrollments), you typically use two JOIN clauses. Each JOIN connects one of the primary tables to the junction table using their respective foreign keys. The order of joins usually doesn't matter for the final result set, but it can sometimes impact performance depending on the database optimizer.

SELECT
    s.student_name,
    c.course_name,
    e.enrollment_date
FROM
    Students s
JOIN
    Enrollments e ON s.student_id = e.student_id
JOIN
    Courses c ON e.course_id = c.course_id
WHERE
    s.student_name = 'Alice Smith';

SQL query to join Students and Courses via Enrollments table

Alternative Join Syntax and Considerations

While INNER JOIN is the most common and often default type for this scenario (as it only returns rows where a match exists in all three tables), you might sometimes need LEFT JOIN or RIGHT JOIN if you want to include records from one of the primary tables even if they don't have a corresponding entry in the junction table. For instance, to list all students and the courses they are enrolled in (if any), you would use a LEFT JOIN from Students to Enrollments.

SELECT
    s.student_name,
    c.course_name
FROM
    Students s
LEFT JOIN
    Enrollments e ON s.student_id = e.student_id
LEFT JOIN
    Courses c ON e.course_id = c.course_id;

SQL query using LEFT JOIN to include all students, even those not enrolled in courses