mysql 外连接时where,on使用时机

引出问题

例题：

1. 查询没有销售记录的商品信息， 和有销售记录的商品信息

测试，用内连接简单得用把两张表相连

SELECT  *  FROM  商品 JOIN  销售表  ON   销售表.商品编号 =商品.商品编号
  

 

外连接两张表相连

 SELECT *  FROM   商品  LEFT  JOIN 销售表   ON 销售表.商品编号 =商品.商品编号
  

 

 

 

ps:多了一行没有销售记录的商品信息.

 

 

结果已经出来了，  即这里因为商品表中g16这个商品编号没有在销售表中匹配，销售表没有g16的销售记录，但是因为外连接，本身就是让:左侧的表(商品)即使某些字段(商品编号)的记录没有与另外一个表(销售表)字段匹配也会显示,  ps: 所以商品表中商品编号为G16的这条记录会显示。也可以理解为什么右侧表销售表为什么这行为空,【没有与左表匹配的信息】

 

这里单纯测试一下: 在商品中 商品编号为16的记录符合上图。

 

抛出问题，            主要          下面 

现在是要查询的是没有销售记录的商品信息。就是把为null的那一条记录过滤出来。

正解:

 SELECT *  FROM   商品  LEFT  JOIN 销售表  ON  销售表.商品编号 =商品.商品编号
  WHERE  销售表.数量 IS NULL

 

 

ps:以前自己的写法:

为什么不可以表连接时  on   后面直接跟过滤条件呢？？？

SELECT *  FROM   商品  LEFT  JOIN 销售表  ON  销售表.商品编号 =商品.商品编号
  AND 销售表.数量 IS NULL

结果销售表不为空的记录也显示出来了， 全部以null填充。这样不符合要求

 

这样可能不好理解，直接测试  left  join   后面    on +过滤条件   和  where +过滤条件

where  过滤

sql:执行顺序;      1.join left   on 根据条件相连时,没有其他的过滤条件，生成临时表

                           2.临时表生成后(就是两表相连的大表)，再加过滤条件  where ，此时  不符合过 where  条件  的 移除

                         3.select 查询

 

on相连时 +and过滤

sql 执行顺序:                   1.join  left on 根据条件相连时，即正在生成临时表时 and条件过滤，不管and条件是真假，左边的记录都显示，     2.select  查询 

   结果销售省市不是山西的全部以null填充，并没有过滤成一条我们想要的记录

 

总结:              其实就是  left  join相连    where  与on  的执行时机不同

在使用left join时，on和where条件的区别如下： 1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录,右表不符合条件null填充 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义了，临时表已经生成好了，条件不为真的就全部过滤掉