MySQL的join on和 where 的执行顺序和区别，以及各种连接说明

目录

1、各种连接的解读说明：

1.1、各种连接的含义和说明

连接 语法 作用
左外连接 left join 返回左表中所有的记录以及右表中连接字段相等的记录
右外连接 right join 返回右表中所有的记录以及左表中连接字段相等的记录。
全外连接 full join 返回两个表中的行：left join + right join（重合部分的相等数据只保留一份）
自连接
笛卡尔积 cross join（不带 WHERE 子句） 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
内连接 inner join 又叫等值连接，只返回两个表中连接字段相等的行。
自然连接 natrual join 和内连接很类似，区别在于结果的重复列上：
自然连接重复列只保存一列（列去重），内连接重复列都保存（列不去重）

1.1.1 所有连接分类

说明：

  • 等值连接和不等连接的区别：都是内连接，等值连接有on条件，不等连接没有on条件

1.1.2 left join 和 left outer join 区别

简单来说就是没有区别，只是写法不同。（ sql-92 标准中应为 LEFT Outer Join，而 SQLServer 中两种都支持的。 ）

参考： 请说明 left join 和 left outer join 的区别 - CSDN 论坛

1.2、神图参考

1.4、一些参考说明

2、on 和 where 条件的区别

2.1 on 和 where 条件的区别 （只对外连接有区别）

前提说明：数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

  • on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
  • where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

即

  • 如果条件中同时有on和where 条件：
    • SQL的执行实际是两步
      • 第一步：根据on条件得到一个临时表
      • 第二步：根据where 条件对上一步的临时表进行过滤，得到最终返回结果。
  • 如果条件中只有on：
    • 那么得到的临时表就是最终返回结果

主要内容来自： sql (join on 和 where 的执行顺序） - higirle - 博客园 ，具体demo也可以看原文。

2.2 具体连接的区别

2.2.1 对于内连接没区别

因为内连接生成的临时表中只会保留符合on条件的数据，所以数据在 on 和 where 条件中过滤没区别。

2.2.2 对于外连接（左连接，右连接，全连接）有区别

而外连接生成的临时表中会保留不符合on条件的数据，对于这些数据，在 on 和 where 条件中过滤就区别了。

2.3 多张（>=3）表连接更显 on 和 where 条件的区别

sql 语句中 join on 和 where 用法的区别和联系 - 一个破名想半年的个人空间 - OSCHINA
注：文中的一个观点和说明很神奇。
总结下：如果是多张表连接，那么在生成临时表的过程中过滤数据和对生成后的临时表过滤数据就有明显区别了。
这种情况下： inner join on 比直接 where 的查询效率要高

3、结束

posted @ 2019-11-07 16:11  不无聊  阅读(13904)  评论(1)  编辑  收藏  举报