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

本文详细介绍了SQL中的笛卡尔积概念及驱动表与被驱动表的工作原理。深入探讨了内连接、外连接(包括左连接和右连接)、全连接等不同类型的连接操作及其应用场景。此外,还对比了where和on子句在查询中的区别。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目录

了解一下什么是笛卡尔积

驱动表和被驱动表

内连接

外连接(常用)

左连接(左外连接)

右连接(右外连接)

全连接 

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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值