`
virusfu
  • 浏览: 183076 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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

 
阅读更多

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

  • 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

分享到：
| java获取上一天日期
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MySQL Left JOIN时指定NULL列返回特定值详解

    总的来说，根据具体需求和情况，你可以选择使用`IFNULL`、`COALESCE`或`IF`函数来处理LEFT JOIN后可能出现的NULL值，使查询结果更加符合预期。在处理大量数据和复杂查询时，理解并熟练运用这些函数对于优化SQL查询和...

    Mysql之innerjoin,leftjoin,rightjoin详解.pdf

    Mysql 之 inner join, left join, right join 详解 Mysql 中的连接查询是指从两个或多个表中检索数据的操作。其中，inner join、left join 和 right join 是三种最常用的连接查询方式。本文将详细解释这三种连接...

    关于sql的left join,right join,inner join,outerjoin

    而OUTER JOIN在需要全面了解两边数据匹配情况时使用。 了解这些JOIN类型后，你可以根据业务需求选择最合适的连接方式，有效地组合和分析来自多个表的数据。在进行复杂的多表查询时，熟练掌握JOIN操作至关重要，它...

    mysql Join使用以及优化

    接下来，关于DBA不让使用Join的问题，其主要原因是不恰当的Join操作可能会导致查询效率极低。如果表很大或者数据没有合适索引，Join操作会消耗巨大的资源，导致性能问题。在没有索引或者索引设计不合理的情况下，...

    mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

    MySQL中的`NOT IN`, `LEFT JOIN`, `IS NULL`, 和 `NOT EXISTS` 是四种不同的SQL查询方式，它们在特定情况下可以实现相似的功能，但实际执行效率可能会有很大差异。本文主要探讨这四种方法在处理大数据量时的性能表现...

    MySQL left join操作中on和where放置条件的区别介绍

    如果`WHERE` 子句包含的条件与`LEFT JOIN`的连接条件相同，但放在`WHERE`里，那么这个条件将在临时表生成后才被检查，这可能导致左表的某些行因不满足`WHERE`条件而被过滤掉，失去了`LEFT JOIN`应有的效果。...

    深入理解mysql之left join 使用详解

    关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒 ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。 如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 ...

    MySQL JOIN 语法说明与 INNER JOIN 语法用法实例.docx

    本文档详细介绍了 MySQL 中的 JOIN 语法，包括 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 等，并提供了实际的实例来说明 JOIN 的用法。 首先，MySQL 的 JOIN 语法用于根据两个或多个表中的字段之间的关系，从...

    解析mysql left( right ) join使用on与where筛选的差异

    MySQL中的LEFT JOIN和RIGHT JOIN是进行表连接查询的两种常见方式，它们用于在两个表之间根据一定的条件进行数据匹配。LEFT JOIN称为左连接，它会返回左表（即第一个表）的所有记录，以及右表（即第二个表）匹配的...

    MySQL的LEFT JOIN表连接的进阶学习教程

    MySQL的LEFT JOIN是一种关联查询操作，它用于合并两个或多个表的数据，并且返回所有左表（也就是在JOIN语句中位于LEFT关键字之后的表）的记录，即使在右表中没有匹配的记录。这个概念是数据库关系查询中的关键部分，...

    MySQL联表查询基本操作之left-join常见的坑

    这篇文章将深入探讨MySQL中LEFT JOIN的基本操作以及可能出现的问题。 首先，我们来看一个简单的LEFT JOIN示例，假设我们有两个表：`role`（角色表）和`user`（用户表），它们通过`role_id`字段关联。`role`表存储...

    MySQL在右表数据不唯一的情况下使用left join的方法

    mysql left join 语句格式 A LEFT JOIN B ON 条件表达式 left join 是以A表为基础，A表即左表，B表即右表。 左表(A)的记录会全部显示，而右表(B)只会显示符合条件表达式的记录，如果在右表(B)中没有符合条件的记录，...

    mysql_adv_select.rar_any left join_union

    总结起来，`LEFT JOIN UNION ALL`的组合使用在处理多表数据时提供了极大的灵活性，尤其在需要根据用户需求动态获取和展示数据库信息的CMS系统中。熟练掌握这种技术，不仅可以提升开发效率，还能保证系统的响应速度和...

    MySQL 多表关联一对多查询实现取最新一条数据的方法示例

    产品告诉我说需要导出客户信息数据，需要导出客户的 所属行业，纳税性质 数据；但是这两个字段却在订单表里面，每次客户下单都会要求客户填写；由此可知，客户数据和订单数据是一对多的关系；那这样的话，问题就来了...

    MySQL表LEFT JOIN左连接与RIGHT JOIN右连接的实例教程

    MySQL中的表连接是数据库操作中的核心概念，尤其在处理多表关联的数据时显得尤为重要。本文将深入探讨LEFT JOIN和RIGHT JOIN两种特殊的连接方式，帮助初学者理解这两种连接的语法、用法及其在实际操作中的应用。 ...

Magicbox
Global site tag (gtag.js) - Google Analytics