关于 MySQL LEFT JOIN 你可能需要了解的三点

最新推荐文章于 2024-08-15 01:26:58 发布
最新推荐文章于 2024-08-15 01:26:58 发布
阅读量555 收藏
点赞数
文章标签： 数据库 c/c++

理解深刻，但我敢打赌，这篇文章肯定能让你学会点东西！

  • ON 子句与 WHERE 子句的不同
  • 一种更好地理解带有 WHERE ... IS NULL 子句的复杂匹配条件的简单方法
  • Matching-Conditions 与 Where-conditions 的不同

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从B表中检索数据行。

如果B表中没有任何一行数据匹配ON的条件,将会额外生成一行所有列为NULL的数据

在匹配阶段WHERE子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

让我们看一个LFET JOIN示例：

01 mysql>CREATETABLE`product` (
02 `id`int(10) unsignedNOTNULLauto_increment,
03 `amount`int(10) unsigneddefaultNULL,
04 PRIMARYKEY(`id`)
05 ) ENGINE=MyISAM AUTO_INCREMENT=5DEFAULTCHARSET=latin1
06
07 mysql>CREATETABLE`product_details` (
08 `id`int(10) unsignedNOTNULL,
09 `weight`int(10) unsigneddefaultNULL,
10 `exist`int(10) unsigneddefaultNULL,
11 PRIMARYKEY(`id`)
12 ) ENGINE=MyISAMDEFAULTCHARSET=latin1
13
14 mysql>INSERTINTOproduct (id,amount)
15 VALUES(1,100),(2,200),(3,300),(4,400);
16 Query OK, 4rowsaffected (0.00 sec)
17 Records: 4 Duplicates: 0 Warnings: 0
18
19 mysql>INSERTINTOproduct_details (id,weight,exist)
20 VALUES(2,22,0),(4,44,1),(5,55,0),(6,66,1);
21 Query OK, 4rowsaffected (0.00 sec)
22 Records: 4 Duplicates: 0 Warnings: 0
23
24 mysql>SELECT*FROMproduct;
25 +----+--------+
26 | id | amount |
27 +----+--------+
28 | 1 | 100 |
29 | 2 | 200 |
30 | 3 | 300 |
31 | 4 | 400 |
32 +----+--------+
33 4rowsinset(0.00 sec)
34
35 mysql>SELECT*FROMproduct_details;
36 +----+--------+-------+
37 | id | weight | exist |
38 +----+--------+-------+
39 | 2 | 22 | 0 |
40 | 4 | 44 | 1 |
41 | 5 | 55 | 0 |
42 | 6 | 66 | 1 |
43 +----+--------+-------+
44 4rowsinset(0.00 sec)
45
46 mysql>SELECT*FROMproductLEFTJOINproduct_details
47 ON(product.id = product_details.id);
48 +----+--------+------+--------+-------+
49 | id | amount | id | weight | exist |
50 +----+--------+------+--------+-------+
51 | 1 | 100 |NULL|NULL|NULL|
52 | 2 | 200 | 2 | 22 | 0 |
53 | 3 | 300 |NULL|NULL|NULL|
54 | 4 | 400 | 4 | 44 | 1 |
55 +----+--------+------+--------+-------+
56 4rowsinset(0.00 sec)

ON 子句和 WHERE 子句有什么不同？

一个问题：下面两个查询的结果集有什么不同么？

1 1.SELECT*FROMproductLEFTJOINproduct_details
2 ON(product.id = product_details.id)
3 ANDproduct_details.id=2;
4 2.SELECT*FROMproductLEFTJOINproduct_details
5 ON(product.id = product_details.id)
6 WHEREproduct_details.id=2;

用例子来理解最好不过了：

01 mysql>SELECT*FROMproductLEFTJOINproduct_details
02 ON(product.id = product_details.id)
03 ANDproduct_details.id=2;
04 +----+--------+------+--------+-------+
05 | id | amount | id | weight | exist |
06 +----+--------+------+--------+-------+
07 | 1 | 100 |NULL|NULL|NULL|
08 | 2 | 200 | 2 | 22 | 0 |
09 | 3 | 300 |NULL|NULL|NULL|
10 | 4 | 400 |NULL|NULL|NULL|
11 +----+--------+------+--------+-------+
12 4rowsinset(0.00 sec)
13
14 mysql>SELECT*FROMproductLEFTJOINproduct_details
15 ON(product.id = product_details.id)
16 WHEREproduct_details.id=2;
17 +----+--------+----+--------+-------+
18 | id | amount | id | weight | exist |
19 +----+--------+----+--------+-------+
20 | 2 | 200 | 2 | 22 | 0 |
21 +----+--------+----+--------+-------+
22 1 rowinset(0.01 sec)

第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。

第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

再来看一些示例：

01 mysql>
02 mysql>SELECT*FROMproductLEFTJOINproduct_details
03 ONproduct.id = product_details.id
04 ANDproduct.amount=100;
05 +----+--------+------+--------+-------+
06 | id | amount | id | weight | exist |
07 +----+--------+------+--------+-------+
08 | 1 | 100 |NULL|NULL|NULL|
09 | 2 | 200 |NULL|NULL|NULL|
10 | 3 | 300 |NULL|NULL|NULL|
11 | 4 | 400 |NULL|NULL|NULL|
12 +----+--------+------+--------+-------+
13 4rowsinset(0.00 sec)

所有来自product表的数据行都被检索到了，但没有在product_details表中匹配到记录（product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据）

01 mysql>SELECT*FROMproductLEFTJOINproduct_details
02 ON(product.id = product_details.id)
03 ANDproduct.amount=200;
04 +----+--------+------+--------+-------+
05 | id | amount | id | weight | exist |
06 +----+--------+------+--------+-------+
07 | 1 | 100 |NULL|NULL|NULL|
08 | 2 | 200 | 2 | 22 | 0 |
09 | 3 | 300 |NULL|NULL|NULL|
10 | 4 | 400 |NULL|NULL|NULL|
11 +----+--------+------+--------+-------+
12 4rowsinset(0.01 sec)

同样，所有来自product表的数据行都被检索到了，有一条数据匹配到了。

使用WHERE ... IS NULL子句的LEFT JOIN

当你使用WHERE ... IS NULL子句时会发生什么呢？

如前所述，WHERE 条件查询发生在 匹配阶段之后，这意味着WHERE ... IS NULL子句将从匹配阶段后的数据中过滤掉不满足匹配条件的数据行。

纸面上看起来很清楚，但是当你在ON子句中使用多个条件时就会感到困惑了。

我总结了一种简单的方式来理解上述情况：

  • 将 IS NULL 作为否定匹配条件
  • 使用!(A and B) == !A OR !B逻辑判断

看看下面的示例：

01 mysql>SELECTa.*FROMproduct aLEFTJOINproduct_details b
02 ONa.id=b.idANDb.weight!=44ANDb.exist=0
03 WHEREb.idISNULL;
04 +----+--------+
05 | id | amount |
06 +----+--------+
07 | 1 | 100 |
08 | 3 | 300 |
09 | 4 | 400 |
10 +----+--------+
11 3rowsinset(0.00 sec)

让我们检查一下ON匹配子句：

1 (a.id=b.id)AND(b.weight!=44)AND(b.exist=0)

我们可以把IS NULL子句 看作是否定匹配条件。

这意味着我们将检索到以下行：

1 !( exist(b.id that equalstoa.id)ANDb.weight !=44ANDb.exist=0 )
2 !exist(b.id that equalstoa.id) || !(b.weight !=44) || !(b.exist=0)
3 !exist(b.id that equalstoa.id) || b.weight =44 || b.exist=1

就像在C语言中的逻辑 AND 和 逻辑 OR表达式一样，其操作数是从左到右求值的。如果第一个参数做够判断操作结果，那么第二个参数便不会被计算求值（短路效果）

看看别的示例：

01 mysql>SELECTa.*FROMproduct aLEFTJOINproduct_details b
02 ONa.id=b.idANDb.weight!=44ANDb.exist=1
03 WHEREb.idISNULL;
04 +----+--------+
05 | id | amount |
06 +----+--------+
07 | 1 | 100 |
08 | 2 | 200 |
09 | 3 | 300 |
10 | 4 | 400 |
11 +----+--------+
12 4rowsinset(0.00 sec)

Matching-Conditions与Where-conditions之战

如果你吧基本的查询条件放在ON子句中，把剩下的否定条件放在WHERE子句中，那么你会获得相同的结果。

例如，你可以不这样写：

1 SELECTa.*FROMproduct aLEFTJOINproduct_details b
2 ONa.id=b.idANDb.weight!=44ANDb.exist=0
3 WHEREb.idISNULL;

你可以这样写：

1 SELECTa.*FROMproduct aLEFTJOINproduct_details b
2 ONa.id=b.id
3 WHEREb.idisnullORb.weight=44ORb.exist=1;

01 mysql>SELECTa.*FROMproduct aLEFTJOINproduct_details b
02 ONa.id=b.id
03 WHEREb.idisnullORb.weight=44ORb.exist=1;
04 +----+--------+
05 | id | amount |
06 +----+--------+
07 | 1 | 100 |
08 | 3 | 300 |
09 | 4 | 400 |
10 +----+--------+
11 3rowsinset(0.00 sec)

你可以不这样写：

1 SELECTa.*FROMproduct aLEFTJOINproduct_details b
2 ONa.id=b.idANDb.weight!=44ANDb.exist!=0
3 WHEREb.idISNULL;

可以这样写：

1 SELECTa.*FROMproduct aLEFTJOINproduct_details b
2 ONa.id=b.id
3 WHEREb.idisnullORb.weight=44ORb.exist=0;

01 mysql>SELECTa.*FROMproduct aLEFTJOINproduct_details b
02 ONa.id=b.id
03 WHEREb.idisnullORb.weight=44ORb.exist=0;
04 +----+--------+
05 | id | amount |
06 +----+--------+
07 | 1 | 100 |
08 | 2 | 200 |
09 | 3 | 300 |
10 | 4 | 400 |
11 +----+--------+
12 4rowsinset(0.00 sec)

这些查询真的效果一样？

如果你只需要第一个表中的数据的话，这些查询会返回相同的结果集。有一种情况就是，如果你从 LEFT JOIN的表中检索数据时，查询的结果就不同了。

如前所属，WHERE 子句是在匹配阶段之后用来过滤的。

例如：

01 mysql>SELECT*FROMproduct aLEFTJOINproduct_details b
02 ONa.id=b.idANDb.weight!=44ANDb.exist=1
03 WHEREb.idisnull;
04 +----+--------+------+--------+-------+
05 | id | amount | id | weight | exist |
06 +----+--------+------+--------+-------+
07 | 1 | 100 |NULL|NULL|NULL|
08 | 2 | 200 |NULL|NULL|NULL|
09 | 3 | 300 |NULL|NULL|NULL|
10 | 4 | 400 |NULL|NULL|NULL|
11 +----+--------+------+--------+-------+
12 4rowsinset(0.00 sec)
13
14 mysql>SELECT*FROMproduct aLEFTJOINproduct_details b
15 ONa.id=b.id
16 WHEREb.idISNULLORb.weight=44ORb.exist=0;
17 +----+--------+------+--------+-------+
18 | id | amount | id | weight | exist |
19 +----+--------+------+--------+-------+
20 | 1 | 100 |NULL|NULL|NULL|
21 | 2 | 200 | 2 | 22 | 0 |
22 | 3 | 300 |NULL|NULL|NULL|
23 | 4 | 400 | 4 | 44 | 1 |
24 +----+--------+------+--------+-------+
25 4rowsinset(0.00 sec)

总附注：

如果你使用LEFT JOIN来寻找在一些表中不存在的记录，你需要做下面的测试：WHERE 部分的col_name IS NULL(其中 col_name 列被定义为 NOT NULL)，MYSQL 在查询到一条匹配LEFT JOIN条件后将停止搜索更多行（在一个特定的组合键下）。

原文链接/OSChina.NET原创翻译转http://www.oschina.net/question/89964_65912?sort=default&p=3#answers

fw2
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql使用left join需要注意的点
biu_biu_duda的博客
09-17 825
mysql使用left join需要注意的点 含义：LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。 Mysql对于left join采用类似嵌套循环的方式进行处理，例如： SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT) 其中p1是on的过滤条件，缺失则认为rue，p2是where过滤条件，缺失也认为是true，该语句的执行逻辑可以描述为： 复制代
MySQL之left join深入理解
小梁
07-16 1195
1.left join 底层执行原理 SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT) 其中P1是on过滤条件，缺失则认为是TRUE，P2是where过滤条件，缺失也认为是TRUE，该语句的执行逻辑 FOR each row lt in LT {// 遍历左表的每一行 BOOL b = FALSE; FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行，找到满足join
MySQL 多表关联一对多查询实现取最新一条数据的方法示例
01-19
本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考，具体如下： MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据，数据出现重复 由于历史原因，表结构设计不合理；产品告诉我说需要导出客户信息数据，需要导出客户的 所属行业，纳税性质 数据；但是这两个字段却在订单表里面，每次客户下单都会要求客户填写；由此可知，客户数据和订单数据是一对多的关系；那这样的话，问题就来了，我到底以订单中的哪一条数据为准呢？经过协商后一致同意以最新的一条数据为准； 数据测试初始化SQL脚本 DROP TABLE IF EXIST
MySQL中join操作后面的on与where的区别
weixin_43310882
03-20 1704
join关键字的作用是将多个表按一定的条件联合起来，从而可以实现从多个表中获取数据 在join后面可以接on条件和where条件，在这里我主要就是说这两者之间的差别 建立两张简单的用来测试的表并添加数据，如下所示，一张表名为id_name，另一张表名为id_age 首先看看不添加条件——t2.age ='22’时的结果 SELECT * from id_name t1 LEFT JOIN id_age t2 on t1.id = t2.id 得到 1、在这里以left join为例，首先运行where加条
MySQL之join/where的用法
qq_45577911的博客
12-07 7469
join、where关键字都可用于表与表之间的连接，join又分为了内连接和外连接，where又有等值与非等值连接和自身链接。 一、where关键字 连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词 格式：where [表名.]<列名1><比较运算符>[表名.]<列名2>… 比较运算符主要有：= 、> 、< 、>= 、<= 、!= 、<> 还有 and 和between 等 当连接运算符为=时为等值连接，其它的是非
Mysql--两表join时的on和where区别
修行的博客
11-11 1702
两表join时的on和where区别 表A join 表B----条件–on—> 临时表—where—> 结果表 on是作为临时表的筛选条件，where作为最终表的筛选条件 总结一： 1.where 是在两个表join完成后，再附上where条件; 2.and 则是在表连接前过滤A表或B表里面哪些记录符合连接条件，同时会兼顾是left join还是right join。即 假如是左连接的话，如果左边表的某条记录不符合连接条件，那么它不进行连接，但是仍然留在结果集中（此时右边部分的连接结果为NU
join on多个条件的理解
热门推荐
weixin_44457814的博客
06-12 4万+
a表 b表 查询1： 这是一个简单的左连接，左表的数据都会查询在结果中。 查询2： 左连接，on后面跟了2个条件，其中第二个条件对左表进行刷选，可以看到在结果中并没有起作用。因为on的条件筛选是对2张表生成的全连接（笛卡尔积）临时表进行的筛选，这时a.id=4的数据实际上是被去除掉了，因为没有a.id=4且a.id=b.id的数据。但是由于是left join，所以在结果中没有的左表数据a.id=4又被加了上去。 由此可见left join 的时候，on后面对左表的数据加筛选条件是没有用的。 查询3
MySQL Left JOIN时指定NULL列返回特定值详解
09-10
总的来说，根据具体需求和情况，你可以选择使用`IFNULL`、`COALESCE`或`IF`函数来处理LEFT JOIN后可能出现的NULL值，使查询结果更加符合预期。在处理大量数据和复杂查询时，理解并熟练运用这些函数对于优化SQL查询和...
mysql多个left join连接查询用法分析
09-09
在某些场景下，我们可能需要连接多个表来获取全面的数据信息。本篇文章将深入探讨MySQL多个LEFT JOIN连接查询的用法，通过实例来解析其操作技巧和注意事项。 首先，LEFT JOIN的关键在于它会保留左表的所有记录，...
MySQL的LEFT JOIN表连接的进阶学习教程
09-10
MySQL的LEFT JOIN是一种关联查询操作，它用于合并两个或多个表的数据，并且返回所有左表（也就是在JOIN语句中位于LEFT关键字之后的表）的记录，即使在右表中没有匹配的记录。这个概念是数据库关系查询中的关键部分，...
超详细mysql left join,right join,inner join用法分析
09-14
在设计复杂的SQL查询时，JOIN操作通常是必不可少的工具，它们允许你跨越不同的表来获取你需要的信息。通过熟练掌握LEFT JOIN、RIGHT JOIN和INNER JOIN的使用，你可以更有效地从数据库中提取数据，满足各种业务需求。...
MySQL left join操作中on和where放置条件的区别介绍
12-15
如果`WHERE` 子句包含的条件与`LEFT JOIN`的连接条件相同，但放在`WHERE`里，那么这个条件将在临时表生成后才被检查，这可能导致左表的某些行因不满足`WHERE`条件而被过滤掉，失去了`LEFT JOIN`应有的效果。...
mysql查询语句join、on、where执行顺序
lihongbao80的专栏
02-18 9849
MySQL 的执行顺序 一、典型SELECT语句完整的执行顺序 1）from子句组装来自不同数据源的数据； 2）使用on进行join连接的数据筛选 3）where子句基于指定的条件对记录行进行筛选； 4）group by子句将数据划分为多个分组； 5）cube， rollup 6）使用聚集函数进行计算； 7）使用having子句筛选分组； 8）计算所有的表达式； 9）计算select的字段； 10）使用distinct 进行数据去重 11）使用order by对结果集进行排序。 12）选择TOPN的数据 二
mysql join和where_MySql join on 和 where
weixin_39595537的博客
01-27 220
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。full join:外连接，返回两个表中的行：left join + right join。cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表...
mysql之left join、join的on、where区别看这篇就懂
weixin_44981707的博客
12-06 1万+
mysql之left join、join的on、where区别看这篇就懂 前言： 网上大量关于left join、join的on、where区别其实很多都是错误，本文开始揭晓其中区别所在，该如何使用。 1.准备 建表语句 CREATE TABLE `t_students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class_id` int(11) NOT NULL, `name` varchar(10) NOT NULL, `gender` char
MYSQL之on和where的区别浅谈
xiaoweids的博客
07-12 973
有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。
sql(join中on与where区别)
hoken的专栏
04-20 2180
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。 right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。 inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。 full join:外连接，返回两个表中的行：left join + right join。 cross join:结果是笛卡尔积
mysql查询语句中，是join先执行还是where先执行？
weixin_42599558的博客
12-28 4456
在 MySQL 中，WHERE 子句会先于 JOIN 子句执行。 JOIN 子句用于连接两个或多个表，在 JOIN 子句中可以使用 ON 或 USING 子句来指定连接条件。 JOIN 子句会从两个表中返回符合连接条件的行，这些行会被合并成一个结果集。 而 WHERE 子句用于过滤查询结果集中的行，它可以使用各种条件来过滤结果集中的行。 因此，在 MySQL 中，WHERE 子句会先于 JOIN ...
webjs 数据库 离线读取本地数据库源码-SAAS 本地化及未来之窗行业应用跨平台架构
最新发布
cybersnow精通 28 门计算机语言，凭借其超凡的技术能力，成功开发过上万个应用，广泛涉及政府、商业、个人等众多领域，甚至在检察院、环保局、公安局等专业场景中也大放异彩。不仅熟练掌握单片机和物联网开发，在软件架构设计方面更是独树一帜，自创了跨平台软件
08-15 243
【代码】webjs 数据库 离线读取本地数据库源码-SAAS 本地化及未来之窗行业应用跨平台架构。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值