`

关于mysql left join

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



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. 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;
用例子来理解最好不过了：


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表中检索符合的所有数据行。

ON 后面的条件是针对从表的（left join后面的表），从从表中查出数据，在和主表进行left join，对于 on后有没有括号（），效果是一样的
第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

再来看一些示例：

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 条件并没有匹配到任何数据）


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 逻辑判断
看看下面的示例：

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 匹配子句：

(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)
我们可以把 IS NULL 子句 看作是否定匹配条件。

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

!( 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表达式一样，其操作数是从左到右求值的。如果第一个参数做够判断操作结果，那么第二个参数便不会被计算求值（短路效果）

看看别的示例：

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 子句中，那么你会获得相同的结果。

例如，你可以不这样写：
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;
你可以这样写：
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;

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)
你可以不这样写：

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;
可以这样写：
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;

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 子句是在匹配阶段之后用来过滤的。

例如：

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 条件后将停止搜索更多行（在一个特定的组合键下）。

原文链接 / OSChina.NET原创翻译
http://www.oschina.net/question/89964_65912
分享到：
| maven中的 dependencies 和 dependencyMan ...
评论
发表评论

文章已被作者锁定，不允许评论。

相关推荐

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

    在SQL查询中，LEFT JOIN操作用于连接两个或更多表，并保留左表（即第一个表）的所有记录，即使右表没有匹配的记录。当右表的某些字段在LEFT JOIN后为NULL时，我们可以利用函数来将这些NULL值替换为特定值，如0或其他...

    Mysql之innerjoin,leftjoin,rightjoin详解.pdf

    Mysql 之 inner join、left join、right join 详解 Mysql 中的连接查询是指从多个表中检索数据，并将它们组合成一个结果集。inner join、left join 和 right join 是 Mysql 中三种最常用的连接查询方式。 inner ...

    mysql多个left join连接查询用法分析

    MySQL中的LEFT JOIN是一种关联多个表的方法，用于返回左表的所有记录以及右表匹配的记录。在某些场景下，我们可能需要连接多个表来获取全面的数据信息。本篇文章将深入探讨MySQL多个LEFT JOIN连接查询的用法，通过...

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

    在SQL查询中，`LEFT JOIN` 是一种连接两个或多个表的方法，以便从左表（通常是主表）中返回所有行，即使在右表中没有匹配的行。在这个过程中，`ON` 和 `WHERE` 子句都是用来指定条件的，但它们在查询逻辑中的作用...

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

    本篇文章将深入探讨四种基本的JOIN类型：LEFT JOIN（左连接）、RIGHT JOIN（右连接）、INNER JOIN（内连接）以及OUTER JOIN（外连接）。我们将详细解释这些概念，它们的工作原理以及在实际数据库操作中的应用。 1. ...

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

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

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

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

    mysql Join使用以及优化

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

    超详细mysql left join,right join,inner join用法分析

    MySQL中的JOIN操作是数据库查询中非常重要的一个概念，主要用于在两个或多个表之间建立关联，以便从这些表中检索所需的数据。在这个超详细的分析中，我们将深入探讨LEFT JOIN、RIGHT JOIN以及INNER JOIN的用法。 1....

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

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

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

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

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

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

    本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考，具体如下： MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据，数据出现重复 ...

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

    MySQL LEFT JOIN 语法 SQL（MySQL） LEFT JOIN 会取得左表（table1）全部记录，即使右表（table2）并无对应匹配记录。LEFT JOIN 基本语法如下： ... FROM table1 LEFT JOIN table2 ON condition ... MySQL LEFT ...

    mysql_adv_select.rar_any left join_union

    结合使用`LEFT JOIN`和`UNION ALL`，我们可以构造一个复杂的查询，首先使用`LEFT JOIN`从文章表中获取数据，并关联到分类表以获取对应的分类名称，然后通过`UNION ALL`将不同条件下的查询结果整合在一起。...

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