连接查询

在前文我们介绍了如何通过子查询在多个表中查询数据。除了子查询，连接查询也可用于从多个表中查询数据。我们将通过具体的示例来学习如何使用连接查询。

例如现在有3张表如下：

学生表（表名students）：

id name gender class
1 屈屈 女 1班
2 慧心 男 2班
3 达琳 女 1班
4 森贝 女 2班

成绩表（表名scores）：

student_id subject score
1 创意写作 100
1 时尚美学 100
1 高能物理 29
1 电子竞技 88
2 创意写作 81
2 电子竞技 91
2 高能物理 85
3 创意写作 93
3 时尚美学 65
3 电子竞技 59
4 时尚美学 75
4 电子竞技 83

如果细心的你发现成绩表存在某些“异常”，先不要着急，后文将介绍其原因。

选课表（表名student_courses），记录了学生选了哪些课程：

student_id subject
1 创意写作
1 时尚美学
1 高能物理
2 创意写作
2 电子竞技
2 高能物理
3 创意写作
3 时尚美学
3 电子竞技
4 时尚美学
4 电子竞技
4 高能物理

内连接

由于成绩表中只记录了学生的学号，而没有学生的班级信息，所以我们无法直接通过成绩表查询“1班”的学生成绩。

你可能会疑问：不能在成绩表中记录学生的班级信息吗？答案是可以的，但一般不这样做，因为这会导致信息冗余。

例如我们想要查询“1班”的学生的成绩，可以使用如下SQL语句：

1
2
3
4
5
6
7
8
9
10
SELECT 
    *
FROM 
    students
INNER JOIN 
    scores 
ON 
    students.id = scores.student_id
WHERE 
    class = '1班';

将会得到1班同学的成绩信息，可以看到结果中包含了学生的姓名：

id name gender class student_id subject score
1 屈屈 女 1班 1 创意写作 100
1 屈屈 女 1班 1 时尚美学 100
1 屈屈 女 1班 1 高能物理 29
3 达琳 女 1班 3 创意写作 93
3 达琳 女 1班 3 时尚美学 65
3 达琳 女 1班 3 电子竞技 59

让我们再次观察上述SQL语句，其中关键字INNER JOIN是我们从未遇到过的，其表示内连接。内连接是连接查询中最常用的一种连接方式，其返回两个表中满足连接条件的记录。INNER JOIN后面的表名表示要连接的表，ON后面的条件表示连接条件，即students.id = scores.student_id，表示将学生表中的ID与成绩表中的学生ID进行连接。students.id中的students表示表名，id表示列名，两者通过点号.连接。

其中INNER关键字可以省略，即JOIN等价于INNER JOIN。为了更清晰地表达我们的意图，建议使用INNER JOIN，与后文即将介绍的外连接可以明显区分开。

如果去除WHERE条件，我们可以更好理解内连接的效果，如下SQL所示：

1
2
3
4
5
6
7
8
SELECT 
    *
FROM 
    students
INNER JOIN 
    scores 
ON 
    students.id = scores.student_id

此时将会得到所有学生的成绩信息，然后WHERE class='1班'表示对连接后的结果进行进一步筛选，只保留班级为“1班”的学生的成绩信息。

另外，由于我们使用的是SELECT *，会将两张表的所有列都展示出来，可以看到查询结果中包含了两列学号信息，这是因为两张表中都有学号信息，所以在连接后的结果中会出现两列学号信息。在实际应用中，我们可以通过SELECT具体的列，获取我们想要的信息，例如：

1
SELECT id, name, class, subject, score FROM ....

外连接

除了内连接，连接查询还有外连接。外连接分为左连接（LEFT JOIN）和右连接（RIGHT JOIN），其区别在于左连接会返回左表中的所有记录，即使右表中没有匹配的记录，而右连接会返回右表中的所有记录，即使左表中没有匹配的记录。而内连接只返回两个表中满足连接条件的记录，即两个表中都有的记录。

是不是有点绕？别着急，接下来通过具体的例子，我们将更好地理解外连接的用法，以及其与内连接的差别。

左连接

观察成绩表与选课表，我们注意到，森贝同学虽然选择了高能物理，但是竟然缺考了，所以在scores表中没有森贝同学的高能物理成绩。为了查询出缺考的学生。这时就需要使用左连接。如下SQL所示：

1
2
3
4
5
6
7
8
9
10
11
SELECT
    student_courses.student_id,
    student_courses.subject,
    scores.score
FROM
    student_courses
LEFT JOIN
    scores
ON
    student_courses.student_id = scores.student_id
    AND student_courses.subject = scores.subject

将得到如下结果：

student_id subject score
1 创意写作 100
1 时尚美学 100
1 高能物理 29
2 创意写作 81
2 电子竞技 91
2 高能物理 85
3 创意写作 93
3 时尚美学 65
3 电子竞技 59
4 时尚美学 75
4 电子竞技 83
4 高能物理 NULL

可以看到，森贝同学的高能物理成绩为NULL，表示缺考。

其中第一张表student_courses表示我们称为左表，第二张表scores表示我们称为右表。LEFT JOIN表示左连接，即返回左表中的所有记录，即使右表中没有匹配的记录。ON后面的条件表示连接条件，即student_courses.student_id = scores.student_id AND student_courses.subject = scores.subject，表示将选课表中的学生ID与成绩表中的学生ID进行连接，并且选课表中的课程与成绩表中的课程进行连接。

由于左表森贝同学在左表student_courses有高能物理的选课记录，而在右表scores中没有相关的成绩记录，所以在连接后的结果中，森贝同学的高能物理成绩为NULL。这样我们就能够查询出缺考的学生。

在SELECT子句中，我们使用表名.列名的形式来指定列，以避免两张表列名冲突（例如都有student_id列）。如下SQL在执行时就会报错：

1
2
3
4
5
6
7
8
9
10
11
SELECT
    student_id,
    subject,
    score
FROM
    student_courses
LEFT JOIN
    scores
ON
    student_courses.student_id = scores.student_id
    AND student_courses.subject = scores.subject

执行时将会报以下错误，因为student_id列在两张表中都存在，数据库不知道你想要哪个表中的student_id列。

1
ERROR 1052 (23000): Column 'student_id' in field list is ambiguous

右连接

使用右连接（RIGHT JOIN）的通常用于从右表（即连接条件中指定的右侧表）中获取全部记录，同时从左表中获取匹配的记录。如果需要特别强调右表中数据的完整性，右连接可以非常有用。

例如屈屈同学觉得电子竞技很有意思，奈何选课名额已满，她并未在选课系统里选上电子竞技，但是仍然去蹭课（玩游戏），并参加了考试。所以在选课记录表中，没有屈屈同学的电子竞技的选课记录，但在成绩表中有电子竞技的成绩。

为了查询有哪些同学在选课系统中没有记录，但是在成绩表中有成绩，我们可以使用右连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    scores.student_id AS s_student_id,
    student_courses.student_id AS sc_student_id,
    scores.subject AS s_subject,
    student_courses.subject AS sc_subject,
    scores.score
FROM
    student_courses
RIGHT JOIN
    scores
ON
    student_courses.student_id = scores.student_id
    AND student_courses.subject = scores.subject

查询结果如下，可以看到屈屈同学虽然有电子竞技的成绩，但是在选课记录表中的学号和科目都为NULL，说明她是赠课的！

s_student_id sc_student_id s_subject sc_subject score
1 1 创意写作 创意写作 100
1 1 时尚美学 时尚美学 100
1 1 高能物理 高能物理 29
1 NULL 电子竞技 NULL 88
2 2 创意写作 创意写作 81
2 2 电子竞技 电子竞技 91
2 2 高能物理 高能物理 85
3 3 创意写作 创意写作 93
3 3 时尚美学 时尚美学 65
3 3 电子竞技 电子竞技 59
4 4 时尚美学 时尚美学 75
4 4 电子竞技 电子竞技 83

多表连接

我们再次观察左连接中的例子，其输出内容为：

student_id subject score
1 创意写作 100
1 时尚美学 100
1 高能物理 29
2 创意写作 81
……

可以发现其输出内容中，没有学生姓名，只有学号，这在实际应用中不太直观。这是因为我们当前所连接的两个表中，都没有学生姓名的信息。这时我们可以再连接一个表，即学生表，以便查询学生姓名。如下SQL所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    student_courses.student_id,
    students.name,
    student_courses.subject,
    scores.score
FROM
    student_courses
LEFT JOIN
    scores
ON
    student_courses.student_id = scores.student_id
    AND student_courses.subject = scores.subject
LEFT JOIN
    students
ON
    student_courses.student_id = students.id

这样我们就可以在查询结果中看到学生姓名了，如下所示：

student_id name subject score
1 屈屈 创意写作 100
1 屈屈 时尚美学 100
1 屈屈 高能物理 29
2 慧心 创意写作 81
2 慧心 电子竞技 91
2 慧心 高能物理 85
3 达琳 创意写作 93
3 达琳 时尚美学 65
3 达琳 电子竞技 59
4 森贝 时尚美学 75
4 森贝 电子竞技 83
4 森贝 高能物理 NULL

在实际应用中，我们可以连接任意多张表，但是需要注意随着连接的表的个数增多，SQL查询的性能可能会有所下降。

自连接

在前面所列举的例子中，连接的多张表都是不同的表，但是在实际应用中，我们也可以连接同一张表，这种连接方式称为自连接（SELF JOIN）。自连接通常用于查询同一张表中的相关数据，例如我们想要查询和“屈屈”同班的学生，可以使用自连接，如下SQL所示：

1
2
3
4
5
6
7
8
9
SELECT
    s2.name,
    s2.class
FROM
    students s1
JOIN
    students s2 ON s1.class = s2.class
WHERE
    s1.name = '屈屈'

将得到如下结果：

name class
屈屈 1班
达琳 1班

在自连接中，我们需要使用别名来区分两个表，否则数据库无法区分哪个是左表，哪个是右表。在students s1中，students表示表名，s1表示表别名，两者通过空格分隔。我们使用了两个表别名s1和s2，这两个别名都表示同一张表students。

连接查询与子查询

在某些情况下连接查询和子查询都可以实现相同的功能，例如在上述例子中，我们使用了连接查询来查询与“屈屈”同班的学生的成绩。这里我们也可以使用子查询来实现相同的功能，如下所示：

1
2
3
4
5
6
7
8
9
10
11
SELECT
    name,
    class
FROM
    students
WHERE
    class = (
        SELECT class
        FROM students
        WHERE name = '屈屈'
    )

同样将返回如下数据：

name class
屈屈 1班
达琳 1班