Nested JOINs and Multi-Table Joins

In real-world databases, data is often spread across multiple tables. To retrieve meaningful information, you may need to join more than two tables.

Multi-Table JOINs

• You can chain multiple JOINs together to combine data from several tables.
• JOIN types (INNER, LEFT, RIGHT, FULL) apply to each join individually.

Example – Students with their enrolled courses and scores:

SELECT students.id, students.name, courses.title, scores.score
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN courses ON enrollments.course_id = courses.id
LEFT JOIN scores ON students.id = scores.student_id;

Nested JOINs

• JOINs can be nested using subqueries or parentheses for clarity.
• Useful when you want to first compute an intermediate result before joining with another table.

Example – Nested JOIN with a subquery for enrolled courses:

SELECT students.id, students.name, enrolled_courses.title, scores.score
FROM students
JOIN (    
SELECT enrollments.student_id, courses.title    
FROM enrollments    
JOIN courses ON enrollments.course_id = courses.id
) enrolled_courses ON students.id = enrolled_courses.student_id
LEFT JOIN scores ON students.id = scores.student_id;

Key Points

• Multi-table joins let you combine several related tables in one query.
• Nested joins or subqueries can make queries easier to read or allow intermediate calculations.
• Use LEFT JOIN when some data may be missing in one table, to avoid losing rows from the main table.

‍