`

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

 
阅读更多

转载于：http://www.oschina.net/question/89964_65912?sort=default

即使你认为自己已对 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 示例：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
mysql> CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `amount` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
  
mysql> CREATE TABLE `product_details` (
  `id` int(10) unsigned NOT NULL,
  `weight` int(10) unsigned default NULL,
  `exist` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
  
mysql> INSERT INTO product (id,amount)
       VALUES (1,100),(2,200),(3,300),(4,400);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
  
mysql> INSERT INTO product_details (id,weight,exist)
       VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
  
mysql> SELECT * FROM product;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)
  
mysql> SELECT * FROM product_details;
+----+--------+-------+
| id | weight | exist |
+----+--------+-------+
|  2 |     22 |     0 |
|  4 |     44 |     1 |
|  5 |     55 |     0 |
|  6 |     66 |     1 |
+----+--------+-------+
4 rows in set (0.00 sec)
  
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id);
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 |    4 |     44 |     1 |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

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

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

?
1
2
3
4
5
6
1. SELECT * FROM product LEFT JOIN product_details
         ON (product.id = product_details.id)
         AND   product_details.id=2;
2. SELECT * FROM product LEFT JOIN product_details
         ON (product.id = product_details.id)
         WHERE product_details.id=2;

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
  
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)

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

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

再来看一些示例：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql>
mysql> SELECT * FROM product LEFT JOIN product_details
       ON product.id = product_details.id
       AND product.amount=100;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 | NULL |   NULL |  NULL |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

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

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product.amount=200;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (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 逻辑判断

看看下面的示例：

?
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id AND b.weight!=44 AND b.exist=0
       WHERE b.id IS NULL;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
3 rows in set (0.00 sec)

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

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

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

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

?
1
2
3
!( exist(b.id that equals to a.id) AND b.weight !=44 AND b.exist=0 )
!exist(b.id that equals to a.id) || !(b.weight !=44) || !(b.exist=0)
!exist(b.id that equals to a.id) || b.weight =44 || b.exist=1

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

看看别的示例：

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id AND b.weight!=44 AND b.exist=1
       WHERE b.id IS NULL;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)

Matching-Conditions 与 Where-conditions 之战

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

例如，你可以不这样写：

?
1
2
3
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist=0
WHERE b.id IS NULL;

你可以这样写：

?
1
2
3
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=1;

 

?
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id
       WHERE b.id is null OR b.weight=44 OR b.exist=1;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
3 rows in set (0.00 sec)

你可以不这样写：

?
1
2
3
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id AND b.weight!=44 AND b.exist!=0
WHERE b.id IS NULL;

可以这样写：

?
1
2
3
SELECT a.* FROM product a LEFT JOIN product_details b
ON a.id=b.id
WHERE b.id is null OR b.weight=44 OR b.exist=0;

 

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT a.* FROM product a LEFT JOIN product_details b
       ON a.id=b.id
       WHERE b.id is null OR b.weight=44 OR b.exist=0;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |
+----+--------+
4 rows in set (0.00 sec)

这些查询真的效果一样？

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

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

例如：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> SELECT * FROM product a LEFT JOIN product_details b
       ON a.id=b.id AND b.weight!=44 AND b.exist=1
       WHERE b.id is null;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 | NULL |   NULL |  NULL |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
  
mysql> SELECT * FROM product a LEFT JOIN product_details b
       ON a.id=b.id
       WHERE b.id IS NULL OR b.weight=44 OR b.exist=0;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 |    4 |     44 |     1 |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)

总附注：

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

分享到：
| mysql 查询某一列在哪张表中
评论
发表评论

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

相关推荐

    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多个left join连接查询用法分析

    在某些场景下，我们可能需要连接多个表来获取全面的数据信息。本篇文章将深入探讨MySQL多个LEFT JOIN连接查询的用法，通过实例来解析其操作技巧和注意事项。 首先，LEFT 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 left join,right join,inner join用法分析

    在设计复杂的SQL查询时，JOIN操作通常是必不可少的工具，它们允许你跨越不同的表来获取你需要的信息。通过熟练掌握LEFT JOIN、RIGHT JOIN和INNER JOIN的使用，你可以更有效地从数据库中提取数据，满足各种业务需求。...

    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