MySQL数据库连接查询 join原理

MySQL数据库连接查询join原理

在MySQL中，我们可以通过JOIN操作实现多个关系表的数据联合查询。JOIN操作是通过将两个或多个表中的列进行比较，从而找到它们的共同数据，并将这些数据进行组合返回给用户。

常见的JOIN操作有 INNER JOIN(内连接)、LEFT JOIN(左连接)、RIGHT JOIN(右连接)、FULL JOIN(全连接)等操作。

INNER JOIN

INNER JOIN(内连接)表示只返回两个表中共同存在的记录，也就是说只有当两个表的相关联的字段相同时，才会将它们查询出来。比如下面的例子：

SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id;

这个语句中，我们将table1和table2两个表联合起来查询。通过ON语句指定了它们共同的字段，即id，只会返回那些在两个表中都存在的id值。

LEFT JOIN

LEFT JOIN(左连接)表示返回左表中所有的记录和右表中的共同记录，如果左表中记录没有和右表中的记录匹配上，则右表中的值为空。比如下面的例子：

SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id;

这个语句中，我们将table1和table2两个表联合起来查询。LEFT JOIN表示会返回table1中所有的记录，包括那些在table2中没有记录匹配的记录。

RIGHT JOIN

RIGHT JOIN(右连接)表示返回右表中所有的记录和左表中的共同记录，如果右表中记录没有和左表中的记录匹配上，则左表中的值为空。比如下面的例子：

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.id = table2.id;

这个语句中，我们将table1和table2两个表联合起来查询。RIGHT JOIN表示会返回table2中所有的记录，包括那些在table1中没有记录匹配的记录。

FULL JOIN

FULL JOIN(全连接)表示返回左表和右表中的所有记录，如果某一侧没有和另一侧记录匹配，则返回空值NULL。比如下面的例子：

SELECT *
FROM table1
FULL JOIN table2
ON table1.id = table2.id;

这个语句中，我们将table1和table2两个表联合起来查询。FULL JOIN表示会返回table1和table2中所有的记录。

示例说明

我们假设我们有两个表，一个是学生表，一个是课程表，它们之间的关系是学生可以选修多门课程，一门课程可以被多名学生选修。我们可以使用以下语句来创建这两个表：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE student_courses (
    id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY(student_id) REFERENCES students(id),
    FOREIGN KEY(course_id) REFERENCES courses(id)
);

现在我们向这两个表中插入一些数据：

INSERT INTO students VALUES (1, 'Tom');
INSERT INTO students VALUES (2, 'Jerry');
INSERT INTO students VALUES (3, 'Mary');

INSERT INTO courses VALUES (1, 'Math');
INSERT INTO courses VALUES (2, 'Physics');
INSERT INTO courses VALUES (3, 'Chemistry');

INSERT INTO student_courses VALUES (1, 1, 1);
INSERT INTO student_courses VALUES (2, 1, 2);
INSERT INTO student_courses VALUES (3, 2, 1);
INSERT INTO student_courses VALUES (4, 3, 3);

现在我们来看一下JOIN操作是如何工作的。

首先，我们来看一下INNER JOIN操作，它可以用下面的语句实现：

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

这个语句的作用是将学生表、学生-课程表和课程表联合起来查询。我们使用ON语句来指定这三个表之间的关系，即学生表和学生-课程表之间是通过学生表的id和学生-课程表的student_id进行关联的，学生-课程表和课程表之间是通过学生-课程表的course_id和课程表的id进行关联的。

接下来，我们来看一下LEFT JOIN操作，它可以用下面的语句实现：

SELECT *
FROM students
LEFT JOIN student_courses
ON students.id = student_courses.student_id
LEFT JOIN courses
ON courses.id = student_courses.course_id;

这个语句的作用是将学生表和学生-课程表进行左连接，再将学生-课程表和课程表进行左连接。LEFT JOIN操作可以保证我们能够得到学生表中的所有记录，不论这些记录在学生-课程表中是否有匹配的记录。

最后，我们来看一下RIGHT JOIN操作，它可以用下面的语句实现：

SELECT *
FROM students
RIGHT JOIN student_courses
ON students.id = student_courses.student_id
RIGHT JOIN courses
ON courses.id = student_courses.course_id;

这个语句的作用是将学生表和学生-课程表进行右连接，再将学生-课程表和课程表进行右连接。RIGHT JOIN操作可以保证我们能够得到课程表中的所有记录，不论这些记录在学生-课程表中是否有匹配的记录。

以上就是MySQL数据库连接查询join原理的完整攻略。