从驱动表和被驱动表来快速理解MySQL中的内连接和外连接

目录

了解一下什么是笛卡尔积

驱动表和被驱动表

内连接

外连接（常用）

左连接（左外连接）

右连接（右外连接）

全连接 

where和on的区别

---

了解一下什么是笛卡尔积

假设集合A={a,b}，集合B={c,d}，则两个集合的笛卡尔积为{(a,c),(a,d),(b,c),(b,d)};

对于没有【条件约束】的两张表进行关联查询，如select * from t1,t2，就是从t1中一条条的选取数据，然后全量匹配t2的所有数据，形成一个大的集合，集合的数据量是两表数据量的乘积，我们称之为【笛卡尔积】，如下：

驱动表和被驱动表

在MySQL中，驱动表的选取是有优化机制的，这里我们不做讨论学习；

两张无关的表联合查询，列出所有的可能的结果，如下图：

查询的过程大致如下：

1、选取一张表，我们称之为【驱动表】，从驱动表中开始查询，找到满足条件的数据（如果没有条件就依次全部取出）。

2、根据从驱动表查询的这条数据，以及其他条件，去第二张【被驱动表】中查询，并将结果进行拼接。

3、依次类推，从驱动表获取第二条数据，使用该数据和条件，再次查询【被驱动表】进行查询。

4、整个过程，会查询【驱动表】一次，查询【被驱动表】多次。

 通过上面的过程分析我们可以发现:
驱动表中的每条数据只查询一次，而被驱动表中的数据会被多次重复查询；

        当查询的两张表没有连接条件时，我们必须列举所有的可能性，就会产生上边的一张大表，如果两个表的数据量变大，比如每张表1000条数据，那笛卡尔积，就会扩张到1百万，如果是三张表关联，就必须再乘以1000。

        但是很明显，如果两个表没有任何的关系，我们也不会连接两张表进行查询的，在上边的案例中，很明显有一个关联条件就是部门id，两张表的部门id一致，才是我们想要的结果，如下：

所以多表查询的联查条件就呼之欲出了，比如内连接，外连接等连接条件；

内连接

        内连接使用关键字 【inner join】 或 【join】 来连接两张表。内连接中，【驱动表】是系统优化后自动选取的，会将执行计划中【扫描次数少】的表选做【驱动表】。

然后使用驱动表去被驱动表中进行条件匹配（这个条件是使用on来声明的），会把匹配到的数据全部展示出来；没有匹配的数据是不会展示出来的；

以下三条sql等效：

SELECT * from teacher t ,course c where c.t_id = t.id
SELECT * from teacher t join course c on c.t_id = t.id
SELECT * from teacher t inner join course c on c.t_id = t.id

通俗讲就是根据条件，找到表 A 和 表 B 的数据的交集。

注意：使用【join】关键字后要使用【on】来确定连接条件，而不是【where】。但是，在内连接中的where和on效果是等价的，但是一定要明确【on】用来声明连接条件，【where】是整理的筛选条件的。后面会再讲；

外连接（常用）

内连接和外连接的区别：

• 对于【内连接】中的两个表，若【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，则该记录不会被加入到最后的结果集中。
• 对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中，针对不同的【驱动表的选择】，又可以将外连接分为【左外连接】和【右外连接】。

所以我们可以得出以下结论：

• 对于左外连接查询的结果会包含左表的所有数据(左外连接左表就是驱动表，驱动表的数据会全部保存（展示）下来)
• 对于右外连接查询的结果会包含右表的所有数据(右外连接右表就是驱动表，驱动表的数据会全部保存（展示）下来)

左连接（左外连接）

SQL展示： 会把左表当做驱动表  下面的SQL语句中就是 course表作为驱动表；

常用关键字: left join

SELECT * from course c left join on teacher t c.t_id = t.id

原始表：

 查询的结果：

我们可以从查询的结果中看到，在左表course中第五条数据中的php课程并没有与之匹配的老师，但是结果php课程也显示出来了，同时将不能匹配其他表的字段都置空 【Null】；

我们可以使用一个图形来形容左外连接的效果：

右连接（右外连接）

sql:会把右表当做驱动表  下面的SQL语句中就是 把teacher表当做驱动表

常用关键字  right join

SELECT * from course c right join teacher t on c.t_id = t.id

 原始表：

 查询结果：

 我们可以看到，在右表teacher中第五条数据并没有与之匹配的左表数据，但是结果也显示出来了，同时将不能匹配其他表的字段都置空 【Null】；

我们可以使用一个图形来形容右外连接的效果：

全连接 

mysql中并不支持全连接，但是有些数据库是支持的，比如oracle，使用【full outer join】关键字；

虽然我的 MySQL 不支持此种方式，但是我们可以把左联合右联的结果联合起来，就可以达到取全集的结果；联合查询结果都关键字：union 或者是 union all 

union 会把重复的内容给覆盖，只显示一个；

union all 把联合的表达数据全部合在一起，不管是否重复；

select * from teacher t right join course c on t_id = c.t_id
union
select * from teacher t left join course c on t.id + c.t_id;

where和on的区别

on 是连接查询中的连接条件，就是驱动表中的数据去被驱动表中进行匹配的一种规则（匹配条件），比如 如果on后面是c.id = t.id ，那么驱动表去被驱动表中匹配数据的时候就只会查询c.id = t.id的数据，不会把完整的笛卡尔集全部查询出来；

where 是对查询出来的结果集进行条件筛选，是先查询出所有的结果集然后再使用where来进行筛选的；

在内连接中where和on效果是等价的，但是还是不建议写where；