left join 中 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)

　理解　

select * from a LEFT JOIN b on a.id=a.xid and a.name=a.xname and a.id=2 where a.id=3 and b.xname='abc';

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

select记录中主表部分：不受on影响，只受where影响；(id=2为无效,id=3有效)

select记录中关联表部分：即受on影响（符合on的记录才进入到连接的结果，不符合的为空填充），也受where影响。

• 也就是说：主表的筛选条件须在 where 中，on 后面的筛选条件是仅针对于关联表

性能差异 update 2022-12-01

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

where中条件是过滤结果的记录。   180s还是在执行中