left join整理_left join 一对多

一、left join基本语句：

我们先看两张表，用户表：

mysql> select * from user_test;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
|  1 | 张三 |   1 |  26 |
|  2 | 王五 |   2 |  30 |
|  3 | 李四 |   1 |  33 |
|  4 | 兰儿 |   2 |  30 |
+----+------+-----+-----+

订单表：

mysql> select * from order_test;
+----+------+------------+-------------+---------------------+
| id | u_id | order_name | order_price | order_time          |
+----+------+------------+-------------+---------------------+
|  1 |    1 | 面巾纸     |          10 | 2020-05-10 16:00:46 |
|  2 |    2 | apple      |          30 | 2020-05-11 13:01:08 |
+----+------+------------+-------------+---------------------+
2 rows in set

1、left join：

mysql> select u.name,u.sex,u.age,o.order_name from user_test u left join order_test o on u.id = o.u_id;
+------+-----+-----+------------+
| name | sex | age | order_name |
+------+-----+-----+------------+
| 张三 |   1 |  26 | 面巾纸     |
| 王五 |   2 |  30 | apple      |
| 李四 |   1 |  33 | NULL       |
| 兰儿 |   2 |  30 | NULL       |
+------+-----+-----+------------+
4 rows in set

查询的结果保留user_test表中全部的id数据和选到的某列的数据，order_test.u_id和其他列只保留与user_test表相匹配的数据，而不匹配的b表的id和其他列以null填充。

2、一对多的情况：

当a表有100行数据，而b表有1000行数据，并且b表中的b.id是有重复值的，而且在实际应用过程中，这确实属于正常现象。

我们可以想象a表是用户表，b表是订单表。自然也就想到了一个用户可能多次下单。我们假设b表中的用户id在a表中匹配到50个用户id，但是这50个用户id总订单数是500个。

那么当我们在执行以上sql语句时，就会出现查询的结果550条，为什么呢？

因为当left join 一对多的时候，就会出现将b表中相同匹配的数据填充到查询结果去。
 

mysql> select * from order_test;
+----+------+------------+-------------+---------------------+
| id | u_id | order_name | order_price | order_time          |
+----+------+------------+-------------+---------------------+
|  1 |    1 | 面巾纸     |          10 | 2020-05-10 16:00:46 |
|  2 |    2 | apple      |          30 | 2020-05-11 13:01:08 |
|  3 |    1 | 水杯       |          20 | 2020-05-12 20:24:42 |
+----+------+------------+-------------+---------------------+
3 rows in set

left join一对多查询：

mysql> select u.name,u.sex,u.age,o.order_name from user_test u 
left join order_test o on u.id = o.u_id;
+------+-----+-----+------------+
| name | sex | age | order_name |
+------+-----+-----+------------+
| 张三 |   1 |  26 | 面巾纸     |
| 王五 |   2 |  30 | apple      |
| 张三 |   1 |  26 | 水杯       |
| 李四 |   1 |  33 | NULL       |
| 兰儿 |   2 |  30 | NULL       |
+------+-----+-----+------------+
5 rows in set

3、一对多查询的优化：

从上面的查询语句看出，虽然我们以user_test表为基础表left join order_test表，但是最终查询出来的结果比user_test表多了一行。原因就是一对多的left join。

如何解决上面这种一对多的问题呢？我们可以根据实际情况来做调整。

1）将一对多转换成多一对：

mysql> select u.name,u.sex,u.age,o.order_name,o.order_price from 
order_test o left join user_test u
on o.u_id = u.id;
+------+-----+-----+------------+-------------+
| name | sex | age | order_name | order_price |
+------+-----+-----+------------+-------------+
| 张三 |   1 |  26 | 面巾纸     |          10 |
| 张三 |   1 |  26 | 水杯       |          20 |
| 王五 |   2 |  30 | apple      |          30 |
+------+-----+-----+------------+-------------+
3 rows in set

以多的端为基础表，进行left join。

2）将一对多聚合起来：

mysql> select u.name,u.sex,u.age,count(o.id) from user_test u 
left join order_test o 
on u.id = o.u_id group by u.name,u.sex,u.age;
+------+-----+-----+-------------+
| name | sex | age | count(o.id) |
+------+-----+-----+-------------+
| 兰儿 |   2 |  30 |           0 |
| 张三 |   1 |  26 |           2 |
| 李四 |   1 |  33 |           0 |
| 王五 |   2 |  30 |           1 |
+------+-----+-----+-------------+
4 rows in set

也可以使用group_concat将行转列：

mysql> select u.name,u.sex,u.age,group_concat(o.order_name) from user_test u 
left join order_test o 
on u.id = o.u_id group by u.name,u.sex,u.age;
+------+-----+-----+----------------------------+
| name | sex | age | group_concat(o.order_name) |
+------+-----+-----+----------------------------+
| 兰儿 |   2 |  30 | NULL                       |
| 张三 |   1 |  26 | 面巾纸,水杯                |
| 李四 |   1 |  33 | NULL                       |
| 王五 |   2 |  30 | apple                      |
+------+-----+-----+----------------------------+
4 rows in set

这样聚合后，查询的最终结果是按照user_test表为基础组装的数据。

二、条件连接查询：

以上的sql语句都是全量查询，在面对表数据较大的情况，全量查询是非常耗时的。所以查询过程中，我们一定要运用where子句来限定条件，提高查询效率。在这里有两种方式：

• 一种是关联出数据后，再筛选；
• 一种是先做筛选，然后再关联。

1、结论：

• where中的语句是关联后，再对数据进行筛选；
• on后面的条件是针对关联表的；
• 主表的筛选条件，应该放置在where条件后面；
• 关联表的筛选条件：
  • 如果放置在 on 后面，那么 A 和 B 的连接顺序为：B 表先按条件查询，再与 A 表连接，即先筛选再连接；
  • 如果放置在 where 后面，那么 A 和 B 的连接顺序为：A 与 B 连接后，再从连接表中筛选，即先连接再筛选

 2、示例：

我们看这个left join结果：

mysql> select u.name,u.sex,u.age,o.order_name from user_test u 
left join order_test o on u.id = o.u_id;
+------+-----+-----+------------+
| name | sex | age | order_name |
+------+-----+-----+------------+
| 张三 |   1 |  26 | 面巾纸     |
| 王五 |   2 |  30 | apple      |
| 张三 |   1 |  26 | 水杯       |
| 李四 |   1 |  33 | NULL       |
| 兰儿 |   2 |  30 | NULL       |
+------+-----+-----+------------+
5 rows in set

1）对主表user_test添加筛选条件（sex=1），只能放到where后：

mysql> select u.name,u.sex,u.age,o.order_name from user_test u 
left join order_test o on u.id = o.u_id where u.sex=1;
+------+-----+-----+------------+
| name | sex | age | order_name |
+------+-----+-----+------------+
| 张三 |   1 |  26 | 面巾纸     |
| 张三 |   1 |  26 | 水杯       |
| 李四 |   1 |  33 | NULL       |
+------+-----+-----+------------+
3 rows in set

放在on后面不起作用：

mysql> select u.name,u.sex,u.age,o.order_name from user_test u 
left join order_test o on u.id = o.u_id and u.sex=1;
+------+-----+-----+------------+
| name | sex | age | order_name |
+------+-----+-----+------------+
| 张三 |   1 |  26 | 面巾纸     |
| 张三 |   1 |  26 | 水杯       |
| 王五 |   2 |  30 | NULL       |
| 李四 |   1 |  33 | NULL       |
| 兰儿 |   2 |  30 | NULL       |
+------+-----+-----+------------+
5 rows in set

及时on后面的条件加括号也一样。on （u.id = o.u_id and u.sex=1） 

2）对关联表添加条件筛选，放在where后，先关联再筛选：

mysql> select u.name,u.sex,u.age,o.order_name,o.order_price from user_test u 
left join order_test o on u.id = o.u_id and o.order_price>=20;
+------+-----+-----+------------+-------------+
| name | sex | age | order_name | order_price |
+------+-----+-----+------------+-------------+
| 王五 |   2 |  30 | apple      |          30 |
| 张三 |   1 |  26 | 水杯       |          20 |
| 李四 |   1 |  33 | NULL       | NULL        |
| 兰儿 |   2 |  30 | NULL       | NULL        |
+------+-----+-----+------------+-------------+
4 rows in set

可以看到这里先把关联表进行了筛选，然后再left join主表，join后只出现了4条数据（不对关联表筛选，left join后应该是5条）。

3）对关联表添加条件筛选，放在where后，先关联再筛选：

mysql> select u.name,u.sex,u.age,o.order_name,o.order_price from user_test u 
left join order_test o on u.id = o.u_id where o.order_price >=20;
+------+-----+-----+------------+-------------+
| name | sex | age | order_name | order_price |
+------+-----+-----+------------+-------------+
| 张三 |   1 |  26 | 水杯       |          20 |
| 王五 |   2 |  30 | apple      |          30 |
+------+-----+-----+------------+-------------+
2 rows in set

参考：

https://www.jianshu.com/p/db050b8914b2

https://blog.csdn.net/qq_30038111/article/details/79740391