mysqljoin多个条件_数据库leftjoin中多个条件需要特殊注意之
sql 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的右边表添加条件的话是⽣效的！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。