MySQL操作实战（三）：表联结

1. 简单联结

①内部联结

内部联结又称为等值联结。ANSI SQL规范首选INNER JOIN语法 。
内部联结分为隐式内联结和显式内联结，二者查询效果相同，仅仅是语法不一样而已。

隐式内联结：WHERE子句
FROM tb1_name, tb2_name, tb3_name
WHERE condition_1 AND condition_2
显式内联结：INNER JOIN语法
FROM tb1_name
INNER JOIN tb2_name ON condition_1
INNER JOIN tb3_name ON condition_2

应用场景：统计参加了考试的学生及其各科成绩

# 隐式内联结
mysql> SELECT a.stu_id, stu_name, a.lesson_id, lesson_name, score
    -> FROM t_score a, t_stu_profile b, t_lesson c
    -> WHERE a.stu_id = b.stu_id AND a.lesson_id = c.lesson_id
    -> ORDER BY stu_id, lesson_id
    -> ;
+--------+----------+-----------+-------------+-------+
| stu_id | stu_name | lesson_id | lesson_name | score |
+--------+----------+-----------+-------------+-------+
|      1 | 郭东     | L001      | 语文        |    90 |
|      1 | 郭东     | L002      | 数据        |    86 |
|      2 | 李西     | L001      | 语文        |    84 |
|      2 | 李西     | L002      | 数据        |    90 |
|      2 | 李西     | L003      | 英语        |    86 |
|      2 | 李西     | L004      | 物理        |    75 |
|      2 | 李西     | L005      | 化学        |    77 |
|      3 | 张北     | L001      | 语文        |   100 |
|      3 | 张北     | L002      | 数据        |    91 |
|      3 | 张北     | L003      | 英语        |    85 |
|      4 | 钱南     | L001      | 语文        |    99 |
|      4 | 钱南     | L002      | 数据        |    88 |
|      4 | 钱南     | L003      | 英语        |    66 |
|      4 | 钱南     | L005      | 化学        |    98 |
+--------+----------+-----------+-------------+-------+

# 显式内联结
mysql> SELECT a.stu_id, stu_name, a.lesson_id, lesson_name, score
    -> FROM t_score a
    -> INNER JOIN t_stu_profile b
    -> INNER JOIN t_lesson c
    -> ON a.stu_id = b.stu_id AND a.lesson_id = c.lesson_id
    -> ORDER BY stu_id, lesson_id
    -> ;

2. 高级联结

①自联结

自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

应用场景：发现成绩表上有个100分的成绩存在问题，需查询100分的这门课程的其它同学的成绩是否存在问题

子查询：先找到100分的这门课程的名称（lesson_id），然后找出这门课程的其他同学的成绩。

mysql> SELECT stu_id, score
    -> FROM t_score
    -> WHERE lesson_id = (
    ->     SELECT lesson_id
    ->     FROM t_score
    ->     WHERE score = 100)
    -> ;
+--------+-------+
| stu_id | score |
+--------+-------+
|      1 |    98 |
|      2 |    84 |
|      3 |   100 |
|      4 |    99 |
+--------+-------+

自联结：此查询中需要的两个表实际上是相同的表，因此t_score表在FROM 子句中出现了两次。虽然这是合法的，但对t_score表的引用具有二义性，因为MySQL不知道引用的是t_score表的哪个实例。

mysql> SELECT a.stu_id, a.score
    -> FROM t_score a, t_score b
    -> WHERE a.lesson_id = b.lesson_id AND b.score = 100
    -> ;
+--------+-------+
| stu_id | score |
+--------+-------+
|      1 |    98 |
|      2 |    84 |
|      3 |   100 |
|      4 |    99 |
+--------+-------+

②自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结返回所有数据，甚至相同的列多次出现。
自然联结排除多次出现，使每个列只返回一次。

通配符*只对表t_stu_profile使用，所有其它列明确列出，所以没有重复的列被检索出来。

mysql> SELECT b.*, a.lesson_id, c.lesson_name, a.score
    -> FROM t_score a, t_stu_profile b, t_lesson c
    -> WHERE a.stu_id = b.stu_id AND a.lesson_id = c.lesson_id
    -> ;

事实上，迄今为止建立的每个内部联结都是自然联结，很可能永远都不会用到不是自然联结的内部联结。

③外部联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候会需要包含没有关联行的那些行。例如，需要对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户。
那么，联结包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

应用场景：统计每位同学的成绩，包括没参加考试的同学

外部联结：外部联结分为左外部联结（LEFT OUTER JOIN）和右外部联结（RIGHT OUTER JOIN）

mysql> SELECT a.stu_id, stu_name, lesson_id, score
    -> FROM t_score a
    -> RIGHT OUTER JOIN t_stu_profile b
    -> ON a.stu_id = b.stu_id
    -> ;
+--------+----------+-----------+-------+
| stu_id | stu_name | lesson_id | score |
+--------+----------+-----------+-------+
|      1 | 郭东     | L001      |    98 |
|      1 | 郭东     | L002      |    86 |
|      1 | 郭东     | L003      |    79 |
|      1 | 郭东     | L004      |    88 |
|      1 | 郭东     | L005      |    98 |
|      2 | 李西     | L001      |    84 |
|      2 | 李西     | L002      |    90 |
|      2 | 李西     | L003      |    86 |
|      2 | 李西     | L004      |    75 |
|      2 | 李西     | L005      |    77 |
|      3 | 张北     | L001      |   100 |
|      3 | 张北     | L002      |    91 |
|      3 | 张北     | L003      |    85 |
|      3 | 张北     | L004      |    79 |
|      3 | 张北     | L005      |    85 |
|      4 | 钱南     | L001      |    99 |
|      4 | 钱南     | L002      |    88 |
|      4 | 钱南     | L003      |    66 |
|      4 | 钱南     | L004      |    66 |
|      4 | 钱南     | L005      |    98 |
|   NULL | 王五     | NULL      |  NULL |
|   NULL | 赵七     | NULL      |  NULL |
+--------+----------+-----------+-------+