left join整理

谁践踏了优雅 2021-08-29 19:48 499阅读 0赞

## 一、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_www.jianshu.com_p_db050b8914b2]

[https://blog.csdn.net/qq\_30038111/article/details/79740391][https_blog.csdn.net_qq_30038111_article_details_79740391]

[https_www.jianshu.com_p_db050b8914b2]: https://www.jianshu.com/p/db050b8914b2
[https_blog.csdn.net_qq_30038111_article_details_79740391]: https://blog.csdn.net/qq_30038111/article/details/79740391