How to Efficiently Join Multiple Tables in MySQL?

Joining Multiple Tables Using MySQL: A Comprehensive Guide

When managing databases, it often becomes necessary to retrieve data from multiple tables. MySQL offers powerful JOIN operators that enable you to combine data from different tables based on common fields.

Join Three Tables in MySQL

In the provided example, the goal is to retrieve student names with the courses they have studied from three tables: Student, Course, and Bridge. The Bridge table acts as an intermediary, linking students to courses.

Using INNER JOIN

To achieve this, use an INNER JOIN syntax, which matches rows from the Student and Course tables based on the values in the Bridge table:

SELECT s.name AS Student, c.name AS Course
FROM student s
INNER JOIN bridge b ON s.id = b.sid
INNER JOIN course c ON b.cid = c.id
ORDER BY s.name;

Normalized Form: Finding Management Relationships

In the second example, you want to determine who manages whom from the employee and manage tables.

Using SQL JOIN Syntax

To retrieve this information in a normalized form:

SELECT e1.name AS Manager, e2.name AS Staff
FROM employee e1
JOIN manage m ON e1.id = m.mid
JOIN employee e2 ON m.eid = e2.id;

ANSI Syntax for Clarity

It's recommended to use ANSI syntax for greater clarity and readability:

SELECT s.name AS Student, c.name AS Course
FROM student s
INNER JOIN bridge b ON s.id = b.sid
INNER JOIN course c ON b.cid = c.id
ORDER BY s.name;

SELECT e1.name AS Manager, e2.name AS Staff
FROM employee e1
JOIN manage m ON e1.id = m.mid
JOIN employee e2 ON m.eid = e2.id;

The above is the detailed content of How to Efficiently Join Multiple Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!