`

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

 
阅读更多

Even if you think you know everything about LEFT JOIN, I bet you will learn something or two in this post!

  • The difference between the ON clause and the WHERE clause.
  • A simple way to better understand a complex Matching-Condition with WHERE … IS NULL clause.
  • The difference between the Matching-Conditions and the Where-conditions.

 

A REMINDER ABOUT “A LEFT JOIN B ON CONDITIONAL_EXPR”

The ON condition (in the expression “A LEFT JOIN B ON conditional_expr”) is used to decide how to retrieve rows from table B (Matching-Stage).
If there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL.
In the Matching-Stage any condition in the WHERE clause is not used. Only after the Matching-Stage, the condition in the WHERE clause will be used. It will filter out rows retrieved from the Matching-Stage.

Lets see a LEFT JOIN example:

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)

IS THERE A DIFFERENCE BETWEEN THE ON CLAUSE AND THE WHERE CLAUSE?



A question: Is there a difference in the result set of the following two queries?

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;

It is best to understand by example:

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)

The first query retrieve all rows from product table while using the ON condition to decide which rows to retrieve from the Left joined product_details table.
The second query is doing simple Left-Join. It filters out rows not matching the WHERE clause conditions.

See more examples:

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)

All the rows from the product table are retrieved. However, no matching found at product_details table (there is no row that matches the condition 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)

All the rows from the product table are retrieved. However, only one matching found at product_details table.

LEFT JOIN WITH WHERE … IS NULL CLAUSE

What is happening if you use the WHERE …. IS NULL clause?
As stated before, the Where-condition stage is happening after the Matching-Stage. This means that the WHERE IS NULL clause will filter out, from the Matching-Stage result, rows that didn’t satisfy the Matching-Condition.
All that is fine on the paper but if you are using more than one condition in the ON clause it is starting to be confusing.

I have developed a simple way to better understand a complex Matching-Condition with WHERE … IS NULL clause by:

  • looking at the IS NULL clause as a negation of the Matching-Condition.
  • using the Logical rule: !(A and B) == !A OR !B

Look at the following example:

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)

Lets examine the Matching clause (ON clause):

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

Remember that we can think of the IS NULL clause as a negation of the Matching-Condition.
This means we will retrieve the following rows:

!( 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

Like in a C programing language, the operands of logical-AND and logical-OR expressions are evaluated from left to right. If the value of the first operand is sufficient to determine the result of the operation, the second operand is not evaluated (short-circuit evaluation).
In our case, this means, retrieve all rows in A that don’t have matching id in B PLUS, for the rows that do have matching id in B, retrieve only the ones that have b.weight =44 OR b.exist=1

See another example:

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)

Explanation:

! ( exist(bid that equals to aid) AND b.weight !=44 AND b.exist=1 )
!exist(bid that equals to aid) || !(b.weight !=44) || !(b.exist=1)
!exist(bid that equals to aid) || b.weight =44 || b.exist=0

THE BATTLE BETWEEN THE MATCHING-CONDITIONS AND THE WHERE-CONDITIONS

You can get the same results (A.*) if you put only the basic matching condition in the ON clause and the negation of the rest in the Where condition clause.
For example,
Instead of writing:

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;

You can write:

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)

Instead of writing:

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;

You can write:

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)

Does these queries really the same?
These queries retrieve the same result set as long as you need only the values from the first table (e.g. A.*). In a case that you are retrieving values from the LEFT JOINed table, the results values are not the same.
As stated before, the condition in the WHERE clause filters out rows retrieved from the Matching-Stage.

For example:

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)

General Note: If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part,
where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after
it has found one row that matches the LEFT JOIN condition.

英文原文来自：

http://www.mysqldiary.com/mysql-left-join/

中文翻译看这里:

http://www.oschina.net/question/89964_65912

分享到：
| Sum of Two Linked Lists - 2
评论
发表评论

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

相关推荐

    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多个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 多表关联一对多查询实现取最新一条数据的方法示例

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

    mysql_adv_select.rar_any left join_union

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

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

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

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