left join 关键字会从左表那里返回所有的行，即使在右表中没有匹配的行。查询中 on 的条件只有一个，因此不存在特殊注意之处。但是当我们 on 条件如果存在多个时候会出现一些与我们预期不符的查询结果。
用户表：
mysql> select * from tab_user;+---------+------+--------+----------+| name | age | sex | addr |+---------+------+--------+----------+| daxin | 18 | male | beijing || mali | 28 | female | shandong || wangsan | 34 | male | beijing || lisi | 45 | male | liaoning || liwu | 58 | female | beijing || maoliu | 43 | male | anhui || zhouba | 62 | female | beijing |+---------+------+--------+----------+7 rows in set (0.00 sec)
订单表：
mysql> select * from tab_order;+-------+-----------+| name | gname |+-------+-----------+| daxin | Smartisan || mali | iPhone || liwu | Mac || lisi | xiaomi || maliu | nike |+-------+-----------+5 rows in set (0.00 sec)
查询
mysql> select * from tab_user u left join tab_order o on u.name=o.name and u.name='lisi';+---------+------+--------+----------+------+--------+| name | age | sex | addr | name | gname |+---------+------+--------+----------+------+--------+| daxin | 18 | male | beijing | NULL | NULL || mali | 28 | female | shandong | NULL | NULL || wangsan | 34 | male | beijing | NULL | NULL || lisi | 45 | male | liaoning | lisi | xiaomi || liwu | 58 | female | beijing | NULL | NULL || maoliu | 43 | male | anhui | NULL | NULL || zhouba | 62 | female | beijing | NULL | NULL |+---------+------+--------+----------+------+--------+7 rows in set (0.00 sec)
咋一看是不是很蒙圈，为什么已经限制了 u.name='lisi' 却查询结果还有其他人呢？如果换用 where 约束.
mysql> select * from tab_user u left join tab_order o on u.name=o.name where u.name='lisi';+------+------+------+----------+------+--------+| name | age | sex | addr | name | gname |+------+------+------+----------+------+--------+| lisi | 45 | male | liaoning | lisi | xiaomi |+------+------+------+----------+------+--------+1 row in set (0.00 sec)
结论
这次确实只有 lisi 了。那为什么第一个查询语句会与预期不符？回顾一下 left join 的定义，左边表会返回所有行，所以 left join 如果对左边表进行约束的话是不会生效的。但是，对 left join 的右边表添加条件的话是生效的！反之，right join 同理！