On条件（在“A left join b on conditional_expr”）决定如何从table B 中检索数据行（Matching-State）;
如果Ｂ中没有行匹配On 条件，额外的B的所有数据列被设为null;
如果Matching-Stage阶段,where语句的任何限制条件都不会使用，只有Match-Stage阶段之后，where语句的条件才回被使用，它会过滤从matching-stage阶段检索出的数据行。
mysql> show create table productG; *************************** 1. row *************************** Table: product Create Table: CREATE TABLE `product` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `amount` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
mysql> show create table product_detailsG; *************************** 1. row *************************** Table: product_details Create Table: 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=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
mysql> select * from productG; *************************** 1. row *************************** id: 1 amount: 100 *************************** 2. row *************************** id: 2 amount: 200 *************************** 3. row *************************** id: 3 amount: 300 *************************** 4. row *************************** id: 4 amount: 400 4 rows in set (0.00 sec)
mysql> select * from product_detailsG; *************************** 1. row *************************** id: 2 weight: 22 exist: 0 *************************** 2. row *************************** id: 4 weight: 44 exist: 1 *************************** 3. row *************************** id: 5 weight: 55 exist: 0 *************************** 4. row *************************** id: 6 weight: 66 exist: 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 | +----+--------+------+--------+-------+ | 2 | 200 | 2 | 22 | 0 | | 4 | 400 | 4 | 44 | 1 | | 1 | 100 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | +----+--------+------+--------+-------+ 4 rows in set (0.00 sec)
on条件语句和where条件语句有区别吗？
A question: 下面两条查询语句结果集有区别吗？
mysql> select * from product left join product_details -> on product.id = product_details.id -> and product_details.id = 2;
mysql> 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.01 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.00 sec)
第一条查询检索出product表的所有行,并用on condition来决定 做左连接的product_details表那些行被检索。
第二条查询做一样的left-JOIN,它用where语句条件过滤掉不匹配的行。
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.03 sec)
product表的所有行都被检索，然而，在product_details表中没找到匹配（没有行匹配该条件product_id = product_details.id and product.amounbt =100）;
mysql> select * from product left join product_details -> on product.id = product_details.id -> and product.amount = 200; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 2 | 200 | 2 | 22 | 0 | | 1 | 100 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | | 4 | 400 | NULL | NULL | NULL | +----+--------+------+--------+-------+ 4 rows in set (0.00 sec)
只有一行在product_details中匹配
left join with where ... is null clause:
where-condition阶段在matching-stage阶段之后发生，这意味这where is null 语句会从matching-stage阶段产生的结果过滤那些行不满足 matching-condition(on ...and)
如果你使用超过一个条件在on 语句中（and....and...）,这时会变的困惑。
一个简单的方法理解复杂的matching-condition 搭配where ... is null 语句：
1： 把 is null 语句看作matching-condition的否定；
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)
检验下matching clause(on clause):
on a.id = b.id and b.weight != 44 and b.exist = 0
记住我们认为is null 语句作为 matching-condition的否定.
这意味着我们检索下面这些行：
! ( 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语言中，&&，||逻辑与，或操作符从左到右计算一样，如果一个操作符的结果足以决定最终结果，第二个操作开始不会计算（短路操作）。
在我们这种情况下，意味着，我们检索与A中所有行不匹配的b表中的id ,如果匹配，再检索b.weight = 44 或者b.exist =1.
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
THE BATTLE BETWEEN THE MATCHING-CONDITIONS AND THE WHERE-CONDITIONS
你可以获得一样的结果（A.*）如果你把基本的匹配条件放在on语句中，而剩余的条件的否定放在where子句中，例如：
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)
同样的结果，不同的写法：
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 | +----+--------+ | 4 | 400 | | 1 | 100 | | 3 | 300 | +----+--------+ 3 rows in set (0.00 sec)
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 | | 2 | 200 | | 3 | 300 | | 4 | 400 | +----+--------+ 4 rows in set (0.00 sec) 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 | +----+--------+ | 2 | 200 | | 4 | 400 | | 1 | 100 | | 3 | 300 | +----+--------+ 4 rows in set (0.00 sec)
这两种查询真的一样吗？
这两种检索一样的结果集如果你只需要第一个表中的结果（e.g. A.*），但是当你在连接表中检索数据时，返回的结果集就跟之前不一样的了，where语句会过滤matching-state返回的数据行；
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 = 1; +----+--------+------+--------+-------+ | id | amount | id | weight | exist | +----+--------+------+--------+-------+ | 4 | 400 | 4 | 44 | 1 | | 1 | 100 | NULL | NULL | NULL | | 3 | 300 | NULL | NULL | NULL | +----+--------+------+--------+-------+ 3 rows in set (0.00 sec) mysql> select * 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 | 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)
如果你用left join来找出那些不存在别的表中的数据行时，在where 语句中的col_name is null部分，col_name对应的列必须被修饰为not null;