MySQL浅谈 LEFT JOIN

    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;