篇首语:本文由小编为大家整理,主要介绍了sql中left join on 多个条件需要特殊注意之处相关的知识,希望对你有一定的参考价值。
定义:
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。例如如下查询:
SELECT column_name(s)FROM table_name1LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
此查询中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)
查询语句1:
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"却查询结果还有非lisi呢?如果换用where约束,查询语句2.
查询语句2:
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了。那为什么查询语句1会与预期不符?回顾一下left join的定义,左边表会返回所有行,所以left join如果对左边表进行约束的话是不会生效的;但是,对left join的右边表添加条件的话是生效的!反之,right join 同理!
以上是关于sql中left join on 多个条件需要特殊注意之处的主要内容,如果未能解决你的问题,请参考以下文章