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 product\G;
*************************** 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_details\G;
*************************** 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 product\G;
*************************** 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_details\G;
*************************** 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;