mysql join 多个条件_数据库left join中多个条件需要特殊注意之处

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的右边表添加条件的话是生效的！