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)