文档章节

MYSQL 中的LEFT( RIGHT ) JOIN使用ON 与WHERE 筛选的差异

文文1
 文文1
发布于 2017/03/21 11:42
字数 1251
阅读 21
收藏 0

有这样的一个问题:查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。

可能只看着两个关键字看不出任何的问题。那我们使用实际的例子来说到底有没有差异。

 

   例如存在两张表结构

   表结构1

Sql代码

DROP TABLE IF EXISTS `A`;
CREATE TABLE `A` (
  `ID` INT(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8

   表结构2

Sql代码

DROP TABLE IF EXISTS `A`;
CREATE TABLE `A` (
  `ID` INT(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8

   表一插入数据

Sql代码

INSERT INTO `A`(id)VALUES(1);  
INSERT INTO `A`(id)VALUES(2);  
INSERT INTO `A`(id)VALUES(3);  
INSERT INTO `A`(id)VALUES(4);  
INSERT INTO `A`(id)VALUES(5);  
INSERT INTO `A`(id)VALUES(6);  

 表二插入数据

Sql代码

INSERT INTO `B`(id)VALUES(1);  
INSERT INTO `B`(id)VALUES(2);  
INSERT INTO `B`(id)VALUES(3); 

 完成后A,B表数据如下:

 语句一

Sql代码

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID <3 

 语句二

Sql代码

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID AND B.ID <3

   以上两个语句的查询结果是否一致。

   我没有注意到这两个查询存在任何差异的【以前也没这么写过sql】。

   我们看看实际结果

   语句一的查询结果

语句二的查询结果为:

 

发现两个查询存在差异。

为什么会存在差异,这和on与where查询顺序有关。

我们知道标准查询关键字执行顺序为 from->where->group by->having->order by

left join 是在from范围类所以 先on条件筛选表,然后两表再做left join。

而对于where来说在left join结果再次筛选。

 第一sql语句:

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID WHERE B.ID <3 

查询过程如下等价于:

    1:先是left join

Sql代码

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID

   查询结果如下

  

2:再查询结果中将B.ID即BID<2筛选出来。

     所以查询结果为:

 

这里可以理解执行过程为:

 (1).A表和B表产生笛卡尔积

SELECT A.ID AS AID,B.ID AS BID FROM A JOIN B

 (2).然后笛卡尔积的结果集通过条件on进行筛选后留下三条记录

SELECT A.ID AS AID,B.ID AS BID FROM A JOIN B ON A.ID = B.ID

 (3).结果集通过left join和A表进行左连接:

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID

 (4).最后左连接后的结果通过where条件进行过滤:

SELECT A.ID AS AID,B.ID AS BID FROM (A LEFT JOIN B ON A.ID = B.ID) WHERE B.ID <3

 

第二sql语句:

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID AND B.ID <3

查询过程如下等价于:

  1:先按照on条件刷选表等价于先筛选B表:

 

   2:再已上查询结果与A表做left join,这也是为什么我们看到第二个查询的sql会保留A表的原因。

所以查询结果为:

  

执行过程为:

(1).首先两表笛卡尔积:

SELECT A.ID AS AID,B.ID AS BID FROM A JOIN B

(2).对笛卡尔积进行on(ON (A.ID = B.ID AND B.ID <3))条件过滤:

SELECT A.ID AS AID,B.ID AS BID FROM A JOIN B ON (A.ID = B.ID AND B.ID <3)

(3).最后对过滤后的结果和A表进行左连接(LEFT JOIN):

所以结果为:

ON与where的使用一定要注意场所:

    (1):ON后面的筛选条件主要是针对的是关联表【而对于主表刷选条件不适用】。

    例如

Sql代码

SELECT A.ID AS AID,B.ID AS BID FROM A LEFT JOIN B ON A.ID = B.ID AND A.ID =3

    这个的查询结果为

    

挺诧异的吧和我们期望的结果不一样,并为筛选出AID=3的数据。

但是我们也发现 AID 与 中AID 1 于2对应的值为NULL,关联表只取了满足A表筛刷选条件的值。

查询过程为:

(1).A表和B表笛卡尔积:

SELECT A.ID AS AID,B.ID AS BID FROM A JOIN B

(2)笛卡尔积后进行on条件过滤:

SELECT A.ID AS AID,B.ID AS BID FROM A JOIN B ON A.ID = B.ID AND A.ID =3

(3)过滤后的结果集和A表再左连接(LEFT JOIN):

所以结果为:

总结:

  ( 1 )   :主表条件在on后面时附表只取满足主表帅选条件的值、而主表还是取整表。

 (2):对于主表的筛选条件应放在where后面,不应该放在ON后面

 (3):对于关联表我们要区分对待。如果是要条件查询后才连接应该把查询件

              放置于ON后。

              如果是想再连接完毕后才筛选就应把条件放置于where后面

 (4): 对于关联表我们其实可以先做子查询再做join

    所以第二个sql等价于

Sql代码

SELECT  A.ID AS AID, B1.ID AS BID  
FROM A LEFT JOIN  ( SELECT B.ID FROM B  WHERE B.ID <3 )B1 ON A.ID = B1.ID   

以上全在mysql5.6上测试过

© 著作权归作者所有

文文1
粉丝 25
博文 449
码字总数 141525
作品 0
长沙
程序员
私信 提问
Mysql left join,right join,inner join的效率比较

一.Join语法概述 join 用于多表中字段之间的联系,语法如下: table1:左表;table2:右表。 JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关...

mickelfeng
2017/10/25
188
0
mysql left/right join on 和where的细小差异

总是在暴漏出bug的时候,才发现自己对知识掌握的不深入。 存在两张表结构 drop table if EXISTS A;CREATE TABLE A (ID int(1) NOT NULL,PRIMARY KEY (ID)) ENGINE=MyISAM DEFAULT CHARSET=ut...

Linland
2015/05/27
128
0
SQL总结

概要 SQL的增删改查操作的对象是数据库表中的记录 SQL语句的要素: 一是指明具体操作的关键字,insert、delete、update、select; 二是表名,缩小目标记录的范围; 三是条件表达式,对表中记...

bithup
2017/12/13
19
0
Mysql 多表联合查询效率分析及优化

多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如: [sql]view plaincopyprint? SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM......

蓝狐乐队
2014/04/30
211
0
mysql基础---左连接、右连接、内连接,union + 常用函数

mysql系列:mysql基础+select5中子句+子查询 mysql查询模型 mysql基础语句+数据类型实例 1、笛卡儿积是什么 1.1、笛卡儿积 现在,我们有两个集合A和B。 A = {0,1} B = {2,3,4} 集合 A×B 和 ...

03/13
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Google Guava 笔记

一、引言 Guava 是 google 几个java核心类库的集合,包括集合 [collections] 、缓存 [caching] 、原生类型支持 [primitives support] 、并发库 [concurrency libraries] 、通用注解 [common ...

SuShine
27分钟前
7
0
SpringBoot中使用@Value为静态变量赋值并测试是否成功

今天想像普通变量一样如下采用写法取配置的,但取到的是个null。。。 @Value("${test.appKey}")private static String appKey; 才发现不能通过这种方式取配置来给static变量赋值 在网上搜索...

SilentSong
27分钟前
5
0
ECMAScript语句之with 语句

ECMAScript with 语句,用于设置代码在特定对象中的作用域(with运行缓慢,设置了属性值时更加缓慢,最好避免使用with语句) 一、with 语句用于字符串(配合toUpperCase()方法) var a = "C...

专注的阿熊
28分钟前
4
0
Apache Flink 进阶(一):Runtime 核心机制剖析

1. 综述 本文主要介绍 Flink Runtime 的作业执行的核心机制。首先介绍 Flink Runtime 的整体架构以及 Job 的基本执行流程,然后介绍在这个过程,Flink 是怎么进行资源管理、作业调度以及错误...

大涛学长
34分钟前
4
0
7. 整数反转

给出一个 32 位的有符号整数,你需要将这个整数中每位上的数字进行反转。 示例 1: 输入: 123 输出: 321 示例 2: 输入: -123 输出: -321 示例 3: 输入: 120 输出: 21 注意: 假设我们的环境只能...

苏坡吴
35分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部