MySql Left join on 与 where比较

LEFT JOIN B ON 条件表达式简述

ON 条件用来决定如何通过表B来获取记录。如果在B中没有记录匹配ON条件，将会生成列值全为NULL的记录。在ON条件匹配阶段，将不会使用WHERE语句。当ON匹配结束后，才会使用WHERE中的条件过滤 经过ON 条件筛选的记录。

LEFT 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=latin1mysql> 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=latin1mysql> 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: 0mysql> 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: 0mysql> 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)

第一个查询返回表product中的记录，同时通过使用ON条件确定返回哪些表product_details中的记录。

第二个查询执行简单的左连接。并通过WHERE语句的条件过滤出结果。

再看一些例子：

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表却没找到匹配的记录。

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表中的记录都被返回，然而在product_details中只有一条匹配的记录被返回。

LEFT JOIN 与 WHERE … IS NULL

如前所述，在Where筛选发生在 ON 匹配阶段之后。这意味着，WHERE IS NULL 语句会过滤出不满足ON匹配阶段匹配条件的记录。当在ON语句中使用多个条件时结果需要仔细分析。

通过下面的方法理解复杂的带有WHERE … IS NULL的匹配条件。

1. 将IS NULL语句看作条件匹配的否定。
2. 使用逻辑规则 !(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)

我们将WHERE中 IS NULL 看作是 ON 语句中对应条件的否定，这意味着我们将获取下列的行，等价的ON表达式如下：

!( 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语言类似，与，或运算符从左向右计算，并且具有短路的特性。

例子：

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)

等价的表达

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

ON 条件匹配与WHERE条件对比

我们可以通过ON及WHERE条件否定来获取A.*
例子：

通过SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.idWHERE b.id is null OR b.weight=44 OR b.exist=1;替代SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.id AND b.weight!=44 AND b.exist=0WHERE b.id IS NULL;

SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.idWHERE b.id is null OR b.weight=44 OR b.exist=0;替代SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.id AND b.weight!=44 AND b.exist!=0WHERE b.id IS NULL;

这些查询真的一样么？

如果仅需要第一个表中的值，这些查询返回的结果一致。如果从所连接表中返回结果，将会有不同的地方。如前所述，WHERE条件会过滤出ON条件匹配后的记录。

例子：

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)

原文链接

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