left join 中 on 与 where 理解，及性能影响

结论先行

用行记录最少做主表；关联表的条件约束，放到 on 子句中，而不是 where 子句中。

示例

mysql> select * from a_company;
+----+---------+------------+
| NO | company | code       |
+----+---------+------------+
| D  | 2371    | 4000023555 |
| A  | 2106    | 4000023556 |
| E  | 2A08    | 5000016000 |
| B  | 2106    | 2000000012 |
| C  | 2106    | 3000023555 |
+----+---------+------------+
5 rows in set (0.02 sec)
 
mysql> select * from a_pice;
+----+-------+------------+--------+
| id | BWKEY | MATNR      | VERPR  |
+----+-------+------------+--------+
|  1 | 2106  | 3000023555 |   2.11 |
|  2 | 2106  | 5000016003 | 1599.9 |
|  3 | 2A08  | 5000016000 | 458.27 |
|  6 | 3769  | 2000000012 | 284.58 |
+----+-------+------------+--------+
4 rows in set (0.03 sec)
 
mysql> select * from a_company c left join a_pice p on c.company=p.bwkey and c.code=p.matnr ORDER BY c.no;
+----+---------+------------+------+-------+------------+--------+
| NO | company | code       | id   | BWKEY | MATNR      | VERPR  |
+----+---------+------------+------+-------+------------+--------+
| A  | 2106    | 4000023556 | NULL | NULL  | NULL       | NULL   |
| B  | 2106    | 2000000012 | NULL | NULL  | NULL       | NULL   |
| C  | 2106    | 3000023555 |    1 | 2106  | 3000023555 |   2.11 |
| D  | 2371    | 4000023555 | NULL | NULL  | NULL       | NULL   |
| E  | 2A08    | 5000016000 |    3 | 2A08  | 5000016000 | 458.27 |
+----+---------+------------+------+-------+------------+--------+
5 rows in set (0.03 sec)
 
mysql> select * from a_company c left join a_pice p on c.company=p.bwkey and c.code=p.matnr where c.NO in ('A','C') ORDER BY c.no;
+----+---------+------------+------+-------+------------+-------+
| NO | company | code       | id   | BWKEY | MATNR      | VERPR |
+----+---------+------------+------+-------+------------+-------+
| A  | 2106    | 4000023556 | NULL | NULL  | NULL       | NULL  |
| C  | 2106    | 3000023555 |    1 | 2106  | 3000023555 |  2.11 |
+----+---------+------------+------+-------+------------+-------+
2 rows in set (0.04 sec)
 
mysql> select * from a_company c left join a_pice p on c.company=p.bwkey and c.code=p.matnr where p.id>2 ORDER BY c.no;
+----+---------+------------+----+-------+------------+--------+
| NO | company | code       | id | BWKEY | MATNR      | VERPR  |
+----+---------+------------+----+-------+------------+--------+
| E  | 2A08    | 5000016000 |  3 | 2A08  | 5000016000 | 458.27 |
+----+---------+------------+----+-------+------------+--------+
1 row in set (0.06 sec)

理解

先left连接，连接方法是on，再对连接的结果进行where筛选；连接的结果：主表全部记录，关联表仅符合连接方法的记录，不符合的为空填充。

• select记录中主表部分：不受on影响，只受where影响；(id=2为无效,id=3有效)
• select记录中关联表部分：即受on影响（符合on的记录才进入到连接的结果，不符合的为空填充），也受where影响。

也就是说：主表的筛选条件须在 where 中，on 后面的筛选条件是仅针对于关联表，但可以减少where中记录数。

性能差异 update 2022-12-01

left join on 中过滤子表的记录。 13.64s显示结果

where中过滤子表的记录。 180s还是在执行中