MySQL8 连接查询/关联查询

连接查询，也叫关联查询，是指多张表（也可以是同一张表和自己关联）基于一定的关系，进行关联查询。在关系数据库中，连接查询是一种非常常见的查询。

常见的连接查询有笛卡尔积、内连接、左外连接、右外连接。

假设student表和teacher表的数据如下：

mysql> use school;
Database changed
mysql> select * from student;
+------------+--------------+--------+------------+-----+------------+
| student_id | student_name | gender | birth_day  | age | teacher_id |
+------------+--------------+--------+------------+-----+------------+
| S01        | 方东美       | 女     | 2006-02-04 |  12 | T01        |
| S02        | 陈顺军       | 女     | 2006-09-12 |  12 | T05        |
| S03        | 陈慧         | 男     | 2004-04-28 |  14 | T02        |
+------------+--------------+--------+------------+-----+------------+
3 rows in set (0.00 sec)

mysql> select * from teacher;
+------------+--------------+--------+
| teacher_id | teacher_name | gender |
+------------+--------------+--------+
| T01        | 高齐妍       | 男     |
| T02        | 李红         | 女     |
| T03        | 李一萱       | 女     |
| T04        | 刘金霞       | 女     |
+------------+--------------+--------+
4 rows in set (0.00 sec)

笛卡尔积

笛卡尔积又叫笛卡尔乘积，是两个集合相乘的结果，是两个集合中所有元素的排列组合。数学定义比较抽象，这里举例说明：

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

在SQL中，笛卡尔积的语法如下：

select
    字段1,字段2,...
from 表1 
cross join 表2;

那么student表和teacher表的笛卡尔积结果如下：

select * 
from student 
cross join teacher;

结果为：

由于student表有3条记录，teacher表有4条记录，从上面的结果来看，返回了student和teacher的所有排列组合3*4=12条。

笛卡尔积是其他所有连接查询返回结果的基础；

内连接

内连接（INNER JOIN）是指返回满足所有连接条件的记录。

语法如下：

select
    字段1,字段2,...
from 表1 
[inner] join 表2
[on 连接条件];

INNER关键字可省略。INNER JOIN与JOIN等价。不过为了SQL语句的可读性，建议使用INNER JOIN。

在内连接中，连接条件可以省略。当连接条件省略时，内连接等价于笛卡尔积。

【示例】查询所有学生信息及对应的老师姓名：

mysql> select
    ->     a.*,b.*
    -> from student a
    -> inner join teacher b
    -> on a.teacher_id = b.teacher_id;
+------------+--------------+--------+------------+-----+------------+------------+--------------+--------+
| student_id | student_name | gender | birth_day  | age | teacher_id | teacher_id | teacher_name | gender |
+------------+--------------+--------+------------+-----+------------+------------+--------------+--------+
| S01        | 方东美       | 女     | 2006-02-04 |  12 | T01        | T01        | 高齐妍       | 男     |
| S03        | 陈慧         | 男     | 2004-04-28 |  14 | T02        | T02        | 李红         | 女     |
+------------+--------------+--------+------------+-----+------------+------------+--------------+--------+
2 rows in set (0.00 sec)

从上面的返回结果来看，仅仅返回了student表和teacher表中teacher_id字段相等的记录。是两张表笛卡尔积的子集。

其他的记录，因为不满足teacher_id字段相等的条件，而没有返回。

左外连接

左外连接（LEFT OUTER JOIN）又叫左连接（LEFT JOIN）。

左连接返回左表中的所有内容外及右表中满足关联条件的部分记录。不满足条件的部分，会被标记为NULL值。

语法如下：

select
    字段1,字段2,...
from 表1 
left [outer] join 表2
on 连接条件;

OUTER关键字可省略，对返回结果没有影响。

与INNER JOIN不同，LEFT JOIN的连接条件不可省略。

【示例】查询老师教授的学生信息：

mysql> select
    ->     a.*,b.*
    -> from school.teacher a
    -> left join school.student b
    -> on a.teacher_id = b.teacher_id;
+------------+--------------+--------+------------+--------------+--------+------------+------+------------+
| teacher_id | teacher_name | gender | student_id | student_name | gender | birth_day  | age  | teacher_id |
+------------+--------------+--------+------------+--------------+--------+------------+------+------------+
| T01        | 高齐妍       | 男     | S01        | 方东美       | 女     | 2006-02-04 |   12 | T01        |
| T02        | 李红         | 女     | S03        | 陈慧         | 男     | 2004-04-28 |   14 | T02        |
| T03        | 李一萱       | 女     | NULL       | NULL         | NULL   | NULL       | NULL | NULL       |
| T04        | 刘金霞       | 女     | NULL       | NULL         | NULL   | NULL       | NULL | NULL       |
+------------+--------------+--------+------------+--------------+--------+------------+------+------------+
4 rows in set (0.00 sec)

返回了所有的老师信息，及满足条件的学生信息。未满足条件的学生信息被赋为NULL值。

【示例】查询学生信息及对应的老师信息：

mysql> select
    ->  a.*,b.*
    -> from school.student a
    -> left join school.teacher b
    -> on a.teacher_id = b.teacher_id;
+------------+--------------+--------+------------+-----+------------+------------+--------------+--------+
| student_id | student_name | gender | birth_day  | age | teacher_id | teacher_id | teacher_name | gender |
+------------+--------------+--------+------------+-----+------------+------------+--------------+--------+
| S01        | 方东美       | 女     | 2006-02-04 |  12 | T01        | T01        | 高齐妍       | 男     |
| S02        | 陈顺军       | 女     | 2006-09-12 |  12 | T05        | NULL       | NULL         | NULL   |
| S03        | 陈慧         | 男     | 2004-04-28 |  14 | T02        | T02        | 李红         | 女     |
+------------+--------------+--------+------------+-----+------------+------------+--------------+--------+
3 rows in set (0.00 sec)

返回了所有的学生信息，及满足条件的老师信息。未满足条件的老师信息被赋为NULL值。

右外连接

右外连接（RIGHT OUTER JOIN）又叫右连接（RIGHT JOIN）。

右连接返回右表中的所有内容外及左表中满足关联条件的部分记录。不满足条件的部分，会被标记为NULL值。

语法如下：

select
    字段1,字段2,...
from 表1 
right [outer] join 表2
on 连接条件;

OUTER关键字可省略，对返回结果没有影响。

与LEFT JOIN一样，RIGHT JOIN的连接条件也不可省略。

【示例】查询老师教授的所有学生信息：

mysql> select
    ->     a.*,b.*
    -> from school.student b
    -> right join school.teacher a
    -> on a.teacher_id = b.teacher_id;
+------------+--------------+--------+------------+--------------+--------+------------+------+------------+
| teacher_id | teacher_name | gender | student_id | student_name | gender | birth_day  | age  | teacher_id |
+------------+--------------+--------+------------+--------------+--------+------------+------+------------+
| T01        | 高齐妍       | 男     | S01        | 方东美       | 女     | 2006-02-04 |   12 | T01        |
| T02        | 李红         | 女     | S03        | 陈慧         | 男     | 2004-04-28 |   14 | T02        |
| T03        | 李一萱       | 女     | NULL       | NULL         | NULL   | NULL       | NULL | NULL       |
| T04        | 刘金霞       | 女     | NULL       | NULL         | NULL   | NULL       | NULL | NULL       |
+------------+--------------+--------+------------+--------------+--------+------------+------+------------+
4 rows in set (0.00 sec)

上面的SQL将LEFT JOIN调整为RIGHT JOIN并互换了左右表的位置，返回结果与LEFT JOIN完全一致。因此，LEFT JOIN与RIGHT JOIN是可以互相转化的。

MySQL不支持全外连接（FULL OUTER JOIN）。