美文网首页
数据库左右连接on后的限制条件问题

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

作者: i_cyy | 来源:发表于2019-06-19 11:50 被阅读0次

    测试环境:

    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

    image

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

    2、t_detailInfo

    image

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

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

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

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

    输出结果如下:

    image

    以上就是我们最常用的左连接语法,但是在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

    查询结果如下:

    image

    (2)对t2.id限制

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

    查询结果如下:

    image

    结果分析:查询结果相同。在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

    查询结果如下:

    image

    结果分析:在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'

    查询结果如下:

    image

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

    结论:

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

    上个图来说明一下

    image

    三、回到我们最初的问题

    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)中的测试语句,我把结果重现拿下来以便对比

    image

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

    image

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

    如果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

    相关文章

      网友评论

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

          本文链接:https://www.haomeiwen.com/subject/kbvhqctx.html