sql中left join on where区别剖析

select from tb1 left join tb2 on tb1.xx=tb2.xx and tb2.xxxx=5
先筛选tb2.xxxx=5 再把tb1与筛选后的临时表进行左连接。

select from tb1 left join tb2 on tb1.xx=tb2.xx where tb2.xxxx=5
先把tb1与tb2进行左连接，连接后产生的临时表筛选出tb2.xxxx=5的记录。

SELECT * FROM tb1;

    id  name        age

------  -------  --------

     1  liuchao        34

     2  lilijun        28

     3  chengyu        44

SELECT * FROM tb2;

    id  name     task

------  -------  --------

     1  liuchao  work

     2  liuchao  study

     3  lilijun  study

SELECT * FROM tb1 LEFT JOIN tb2 ON tb1.name=tb2.name AND tb2.`name`='liuchao';

    id  name        age      id  name     task

------  -------  ------  ------  -------  --------

     1  liuchao      34       1  liuchao  work

     1  liuchao      34       2  liuchao  study

     2  lilijun      28  (NULL)  (NULL)   (NULL)

     3  chengyu      44  (NULL)  (NULL)   (NULL)

SELECT * FROM tb1 LEFT JOIN tb2 ON tb1.name=tb2.name WHERE tb2.`task` IS NOT NULL;

    id  name        age      id  name     task

------  -------  ------  ------  -------  --------

     1  liuchao      34       1  liuchao  work

     1  liuchao      34       2  liuchao  study

     2  lilijun      28       3  lilijun  study