MySQL中的连接查询

超、凢脫俗 2022-09-04 06:44 173阅读 0赞

对于本文章，所用的表为student表和score表：

mysql> create table student(id int, name varchar(20));
    mysql> insert into student values(1, "morris"), (2, "bob"), (3, "tom"), (4, "jack");
    mysql> create table score(id int, score int);
    mysql> insert into score values(1, 80), (2, 90), (4, 100);
    
    mysql> select * from student;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | morris |
    |    2 | bob    |
    |    3 | tom    |
    |    4 | jack   |
    +------+--------+
    4 rows in set (0.00 sec)
    
    mysql> select * from score;
    +------+-------+
    | id   | score |
    +------+-------+
    |    1 |    80 |
    |    2 |    90 |
    |    4 |   100 |
    +------+-------+
    3 rows in set (0.00 sec)

# 笛卡尔积 #

笛卡尔积原本是代数的概念，他的意思是对于两个不同的集合A，B。对于A中的每一个元素，都有对于在B中的所有元素做连接运算 。可以见得对于两个元素分别为m，n的表。笛卡尔积后得到的元素个数为m\*n个元组。而对于mysql来说，默认的连接就是笛卡尔积连接。

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果  
集并返回给用户。

mysql> select * from student,score;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    4 |   100 |
    |    1 | morris |    2 |    90 |
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    4 |   100 |
    |    2 | bob    |    2 |    90 |
    |    2 | bob    |    1 |    80 |
    |    3 | tom    |    4 |   100 |
    |    3 | tom    |    2 |    90 |
    |    3 | tom    |    1 |    80 |
    |    4 | jack   |    4 |   100 |
    |    4 | jack   |    2 |    90 |
    |    4 | jack   |    1 |    80 |
    +------+--------+------+-------+
    12 rows in set (0.00 sec)

连接查询的结果集中包含一个表中的每一条  
记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为  
笛卡尔积。因为表student中有4条记录，表score中有3条记录，所以这两个表连接  
之后的笛卡尔积就有3×4=12行记录。

# 连接的过程 #

在MySQL中，连接查询的语法很随意，只要在FROM语句后边跟多个表名  
就好了，我们可以连接任意数量张表，但是如果没有任何限制条件的话，这些表连接  
起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产  
生的笛卡尔积就有100×100×100=1000000行数据！所以在连接的时候过滤掉特  
定记录组合是有必要的。

在连接查询中的过滤条件可以分成两种，比方说下边这  
个查询语句：

mysql> select * from student st,score sc where st.id > 2 and st.id = sc.id and sc.score >= '80';

*  涉及单表的条件：st.id > 2、sc.score >= ‘80’
 *  涉及两表的条件  
    ：st.id = sc.id

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

1.  首先确定第一个需要查询的表，这个表称之为**驱动表**。单表中执行  
    查询语句只需要选取代价最小的那种访问方法去执行单表查询语句就好了（就是  
    说从 const、ref、ref\_or\_null、range、index、all等等这些执行方法中选取代价最  
    小的去执行查询）。  
    此处假设使用student作为驱动表，那么就需要到student表中找满足st.name = 'morris’的记  
    录，因为表中的数据太少，我们也没在表上建立二级索引，所以此处查询student表  
    的访问方法就设定为all，也就是采用全表扫描的方式执行单表查询。  
    很明显，student表中符合st.name = 'morris’的记录有2条。
2.  针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要  
    到score表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因  
    为是根据student表中的记录去找score表中的记录，所以score表也可以被称之为**被驱动  
    表**。上一步骤从驱动表中得到了2条记录，所以需要查询2次score表。此时涉及  
    两个表的列的过滤条件st.id = sc.id就派上用场了。

从上边两个步骤可以看出来，这个两表连接查询共需要查询1次student表，2  
次score表。也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

# 外连接 #

## 左外连接 ##

左（外）连接的语法还是挺简单的，比如我们要把e1表和e2表进行左外连  
接查询可以这么写：

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

其中中括号里的OUTER单词是可以省略的。对于LEFT JOIN类型的连接来说，  
我们把放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。  
所以上述例子中e1就是外表或者驱动表，e2就是内表或者被驱动表。需要注意  
的是，对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条  
件。

mysql> select * from student st left join score sc on st.id = sc.id;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    2 |    90 |
    |    3 | tom    | NULL |  NULL |
    |    4 | jack   |    4 |   100 |
    +------+--------+------+-------+
    4 rows in set (0.00 sec)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]

## 右外连接 ##

右（外）连接和左（外）连接的原理是一样的，语法也只是把LEFT换成RIGHT  
而已：

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

只不过驱动表是右边的表e2，被驱动表是左边的表e1。

mysql> select * from student st right join score sc on st.id = sc.id;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    2 |    90 |
    |    4 | jack   |    4 |   100 |
    +------+--------+------+-------+
    3 rows in set (0.00 sec)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 1]

## 全连接 ##

MySQL不支持全连接。一定要使用的话可以用左连接+右连接+去重=全连接。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 2]

# 内连接 #

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接  
条件时不会把该记录加入到最后的结果集，一种最简单的内连接语法，就是直接  
把需要连接的多个表都放到FROM子句后边。其实针对内连接，MySQL 提供了  
好多不同的语法：

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

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

SELECT * FROM e1 JOIN e2;
    SELECT * FROM e1 INNER JOIN e2;
    SELECT * FROM e1 CROSS JOIN e2;

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

SELECT * FROM e1, e2;

再说一次，由于在内连接中ON子句和WHERE子句是等价的，所以内连接  
中不要求强制写明ON子句。

我们前边说过，连接的本质就是把各个连接表中的记录都取出来依次匹配的  
组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的笛卡尔  
积肯定是一样的。而对于内连接来说，由于凡是不符合ON子句或WHERE子句  
中的条件的记录都会被过滤掉，其实也就相当于从两表连接的笛卡尔积中把不符  
合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互  
换的，并不会影响最后的查询结果。

但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合  
ON子句条件的记录时也要将其加入到结果集，所以此时驱动表和被驱动表的关  
系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

mysql> select * from student st,score sc where st.id = sc.id;
    +------+--------+------+-------+
    | id   | name   | id   | score |
    +------+--------+------+-------+
    |    1 | morris |    1 |    80 |
    |    2 | bob    |    2 |    90 |
    |    4 | jack   |    4 |   100 |
    +------+--------+------+-------+
    3 rows in set (0.00 sec)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]: /images/20220829/bbd9c86e40514f21a3389434ac7d35e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 1]: /images/20220829/cd504a8074bb41de920145b56151ecaf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 2]: /images/20220829/7f5897b475154aa1b79420c1d796aa56.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 3]: /images/20220829/3d1a4ab4bc2e46088420007720a7e4f4.png