数据库左右连接on后的限制条件问题

测试环境:

MySQL 5.7.19

HeidiSQL 9.3 数据库界面连接工具(挺好用的)

碰到的问题是:

Select * from t1 left outer join t2 on t1.id=t2.id and t2.age>18 和
Select * from t1 left outer join t2 on t1.id=t2.id where t2.age>18 两条语句返回结果集是否相同

之前在on后边只写过两张表的连接条件,没有考虑过左右连接on后面接列值有限制条件,所以仔细分析一下这个限制条件到底是有何效果

 

一、建立测试表格

1、t_basicInfo

表t_basicInfo有3列信息值,第一列标记某唯一的id,后两列是基本信息

2、t_detailInfo

表t_detailInfo有4列信息值,前三列与表t_basicInfo表示相同信息。但此表并没有表t_basicInfo所有的人的信息,只有部分人的详细信息(address)。

 

先来看下我们平时常写的左右连接语句,以左连接为例

#测试易于理解情况下,直接用*把所有列信息输出,在非测试条件下不要用*。

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id

  输出结果如下:

以上就是我们最常用的左连接语法,但是在on后加上限制条件会如何?

 

二、在on后添加除了连接条件外的其他限制条件

测试一下对不同的条件进行限制筛选结果如何:

①对id列(连接列)进行限制

②对t1中name进行限制

③对t2中name进行限制

 

1、对id进行限制

(1)对t1.id限制

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id and t1.id > 2

  查询结果如下:

(2)对t2.id限制

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id and t2.id > 2

  查询结果如下:

 

结果分析:查询结果相同。在on后对连接条件中的列(id)进行限制只会对副表(t2)中的信息进行限制,t1中的数据行数并没有减少

还不能下结论,接着测试

 

2、对t1中的列(非关联列)进行限制

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id and t1.age > 19

  查询结果如下:

 

结果分析:在t1中的列进行限制后,只把满足条件的t1行进行左连接,t1中的数据行数并没有减少。

 

3、对t2中的列(非关联列)进行限制

 

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id and t2.address != 'Shanghai'

  查询结果如下:

 

 

 结果分析:在t2中的列进行限制后,只把t2满足条件的列进行左连接,t1中的数据行数并没有减少。

 

 结论:

  结合1/2/3中的测试结果:如果在on后加上限制条件,会在连接时考虑限制条件,如果不符合条件,则本行数据不进行连接动作。作为主表所有数据必出现在结果集中,而副表不进行连接的行数据一定不出现在结果集中。

上个图来说明一下

  

 

三、回到我们最初的问题

 

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id and t1.id > 2

  和

Select * from t_basicInfo t1 left outer join t_detailInfo t2 on t1.id = t2.id where t1.id > 2

  

如果将and换成where,结果集是什么?

 

第一条语句是二、(1)中的测试语句,我把结果重现拿下来以便对比

然后第二句的结果运行结果如下:

 

仔细考虑一下原因,然后看结论:

  如果on后的and换成了where。where后的限制条件已经不是在t1和t2表进行连接的时候进行限制,而是对 在t1和t2表进行t1.id=t2.id条件下进行关联的结果集再进行select * from(连接结果集) where t1.id>2.

 

 

如有错误,还望指正!

 

转载于:https://www.cnblogs.com/Jacck/p/8213828.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值