连接查询

把多个表查询记录组成新的更大的记录，这个过程就叫做连接查询。连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。

使用 , 连接表即可查询

sql

mysql> select * from t1,t2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    2 | b    |
|    2 | b    |    2 | b    |
|    1 | a    |    2 | b    |
|    3 | c    |    3 | c    |
|    2 | b    |    3 | c    |
|    1 | a    |    3 | c    |
|    3 | c    |    4 | d    |
|    2 | b    |    4 | d    |
|    1 | a    |    4 | d    |
+------+------+------+------+
9 rows in set (0.06 sec)

mysql>

等价写法

写法一：这种写法是将t1、t2表中的列名都显式的写出来，也就是使用了列的全限定名。

sql

SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;

写法二：由于t1、t2表中的列名并不重复，所以没有可能让服务器懵逼的二义性，在查询列表上直接使用列名也是可以的。

sql

SELECT m1, n1, m2, n2 FROM t1, t2;

写法三：这种写法意思就是查询t1表的全部的列，t2表的全部的列。

sql

SELECT t1.*, t2.* FROM t1, t2;

连接的过程

sql

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在这个查询中我们指明了这三个过滤条件：

t1.m1 > 1
t1.m1 = t2.m2
t2.n2 < 'd'

那么这个连接查询的大致执行过程如下：

首先确定第一个需要查询的表，这个表称之为驱动表。此处假设使用 t1 作为驱动表，那么就需要到 t1 表中找满足 t1.m1 > 1 的记录，符合这个条件的t1表记录如下所示：

sql

+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.01 sec)

我们可以看到，t1 表中符合 t1.m1 > 1 的记录有两条。

上一步骤中从驱动表每获取到一条记录，都需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找 t2 表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，也就意味着需要查询2次t2表。此时涉及两个表的列的过滤条件 t1.m1 = t2.m2 就派上用场了：

对于从t1表种查询得到的第一条记录，也就是当t1.m1 = 2, t1.n1 = 'b'时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询，将得到的记录和从t1表中查询得到的第一条记录相组合得到下边的结果：

sql

+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+

对于从t1表种查询得到的第二条记录，也就是当t1.m1 = 3, t1.n1 = 'c'时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询，将得到的记录和从t1表中查询得到的第二条记录相组合得到下边的结果：

sql

+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    3 | c    |
+------+------+------+------+

所以整个连接查询的执行最后得到的结果集就是这样：

sql

+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

从上边两个步骤可以看出来，我们上边唠叨的这个两表连接查询共需要查询1次t1表，2次t2表。当然这是在特定的过滤条件下的结果，如果我们把t1.m1 > 1这个条件去掉，那么从t1表中查出的记录就有3条，就需要查询3次t2表了。也就是说在两表连接查询中，驱动表只需要查询一次，被驱动表可能会被查询多次。

内连接和外连接

上述的连接都是内连接，当驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集时就需要外连接两个表，外连接仍然可以细分为2种：

左外连接选取左侧的表为驱动表。
右外连接选取右侧的表为驱动表。

左连接

sql

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

sql

mysql> select student_info.number, name, major, subject, score from student_info left join student_score on student_info.number = student_score.number;
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
| 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180103 | 范统      | 软件工程                 | 论萨达姆的战争准备          |    61 |
| 20180103 | 范统      | 软件工程                 | 母猪的产后护理              |    59 |
| 20180104 | 史珍香    | 软件工程                 | 论萨达姆的战争准备          |    46 |
| 20180104 | 史珍香    | 软件工程                 | 母猪的产后护理              |    55 |
| 20180105 | 范剑      | 飞行器设计               | NULL                        |  NULL |
| 20180106 | 朱逸群    | 电子信息                 | NULL                        |  NULL |
+----------+-----------+--------------------------+-----------------------------+-------+
10 rows in set (0.09 sec)

mysql>

右连接

sql

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

sql

mysql> select student_info.number ,name,major,subject,score from student_score right join student_info on student_info.number = student_score.number;
+----------+-----------+--------------------------+-----------------------------+-------+
| number   | name      | major                    | subject                     | score |
+----------+-----------+--------------------------+-----------------------------+-------+
| 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理              |    78 |
| 20180101 | 杜子腾    | 计算机科学与工程         | 论萨达姆的战争准备          |    88 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 母猪的产后护理              |   100 |
| 20180102 | 杜琦燕    | 计算机科学与工程         | 论萨达姆的战争准备          |    98 |
| 20180103 | 范统      | 软件工程                 | 母猪的产后护理              |    59 |
| 20180103 | 范统      | 软件工程                 | 论萨达姆的战争准备          |    61 |
| 20180104 | 史珍香    | 软件工程                 | 母猪的产后护理              |    55 |
| 20180104 | 史珍香    | 软件工程                 | 论萨达姆的战争准备          |    46 |
| 20180105 | 范剑      | 飞行器设计               | NULL                        |  NULL |
| 20180106 | 朱逸群    | 电子信息                 | NULL                        |  NULL |
+----------+-----------+--------------------------+-----------------------------+-------+
10 rows in set (0.00 sec)

mysql>

内连接

内连接和外连接的根本区别就是在驱动表中的记录不符合ON 子句中的连接条件时不会把该记录加入到最后的结果集，我们最开始唠叨的那些连接查询的类型都是内连接。

sql

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在MySQL中，下边这几种内连接的写法都是等价的：

SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;

上边的这些写法和直接把需要连接的表名放到FROM语句之后，用逗号,分隔开的写法是等价的

sql

SELECT * FROM t1, t2;

小结

把三种连接方式都写出来，可以对比下

sql

mysql> select * from t1 inner join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 right join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql>

多表连接

sql

mysql> SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.m1 = t2.m2 AND t1.m1 = t3.m3;
+------+------+------+------+------+------+
| m1   | n1   | m2   | n2   | m3   | n3   |
+------+------+------+------+------+------+
|    3 | c    |    3 | c    |    3 | c    |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql>

等价于

sql

SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 INNER JOIN t3 ON t1.m1 = t3.m3;

表的别名

列的别名

sql

mysql> select number as "学号" from student_info;
+----------+
| 学号     |
+----------+
| 20180104 |
| 20180102 |
| 20180101 |
| 20180103 |
| 20180105 |
| 20180106 |
+----------+
6 rows in set (0.03 sec)

mysql>

别名可以用在 ORDER BY、GROUP BY 等子句别名

sql

mysql> select number as "学号" from student_info order by "学号" desc;
+----------+
| 学号     |
+----------+
| 20180104 |
| 20180102 |
| 20180101 |
| 20180103 |
| 20180105 |
| 20180106 |
+----------+
6 rows in set (0.00 sec)

mysql>

表的别名

sql

mysql> select s1.number, s1.name,s1.major, s2.subject, s2.score from student_info as s1 inner join student_score as s2 on s1.number=s2.number limit 1;
+----------+-----------+--------------------------+-----------------------+-------+
| number   | name      | major                    | subject               | score |
+----------+-----------+--------------------------+-----------------------+-------+
| 20180101 | 杜子腾    | 计算机科学与工程         | 母猪的产后护理        |    78 |
+----------+-----------+--------------------------+-----------------------+-------+
1 row in set (0.00 sec)

mysql>

自连接

同一个表的连接，就是自连接。想查看与'史珍香'相同专业的学生有哪些

sql

mysql> select s2.name, s2.number, s2.major from student_info as s1 inner join student_info as s2 where s1.name="史珍香" and s1.major=s2.major;
+-----------+----------+--------------+
| name      | number   | major        |
+-----------+----------+--------------+
| 范统      | 20180103 | 软件工程     |
| 史珍香    | 20180104 | 软件工程     |
+-----------+----------+--------------+
2 rows in set (0.00 sec)

mysql>

子查询和连接查询转换

有的查询需求既可以使用连接查询解决，也可以使用子查询解决

sql

mysql> select * from student_score where number in (select number from student_info where major like "计算机%");
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母猪的产后护理              |    78 |
| 20180101 | 论萨达姆的战争准备          |    88 |
| 20180102 | 母猪的产后护理              |   100 |
| 20180102 | 论萨达姆的战争准备          |    98 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)

mysql> select s1.number, s1.subject,s1.score  from student_score as s1 inner join student_info as s2 on s1.number = s2.number and s2.major like "计算机%";
+----------+-----------------------------+-------+
| number   | subject                     | score |
+----------+-----------------------------+-------+
| 20180101 | 母猪的产后护理              |    78 |
| 20180101 | 论萨达姆的战争准备          |    88 |
| 20180102 | 母猪的产后护理              |   100 |
| 20180102 | 论萨达姆的战争准备          |    98 |
+----------+-----------------------------+-------+
4 rows in set (0.00 sec)

mysql>

总结

使用连接查询关键词就是 join

inner join 内连接
left join 左（外）连接
right join 右（外）连接

连接查询 ​

连接的过程 ​

内连接和外连接 ​

左连接 ​

右连接 ​

内连接 ​

小结 ​

多表连接 ​

表的别名 ​

自连接 ​

子查询和连接查询转换 ​

总结 ​

连接查询

连接的过程

内连接和外连接

左连接

右连接

内连接

小结

多表连接

表的别名

自连接

子查询和连接查询转换

总结