sql中left join on 多个条件需要特殊注意之处

版权声明:本文为博主原创文章,转载标明出处,谢谢! https://blog.csdn.net/Dax1n/article/details/81590910

定义:

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。例如如下查询:

SELECT column_name(s)
FROM table_name1
LEFT 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 同理!

 

展开阅读全文

没有更多推荐了,返回首页