mysql中on怎么用 mysql中on是什么意思( 二 )

17
Records:
4Duplicates: 0Warnings: 0
18
19
mysql INSERT INTO product_details
(id,weight,exist)
20
VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);
21
Query
OK, 4 rows affected
(0.00 sec)
22
Records:
4Duplicates: 0Warnings: 0
23
24
mysql SELECT * FROM product;
25
+----+--------+
26
|
id | amount |
27
+----+--------+
28
|
1 |100 |
29
|
2 |200 |
30
|
3 |300 |
31
|
4 |400 |
32
+----+--------+
33
4 rows in set (0.00
sec)
34
35
mysql SELECT * FROM product_details;
36
+----+--------+-------+
37
|
id | weight | exist |
38
+----+--------+-------+
39
|
2 |22 |0 |
40
|
4 |44 |1 |
41
|
5 |55 |0 |
42
|
6 |66 |1 |
43
+----+--------+-------+
44
4 rows in set (0.00
sec)
45
46
mysql SELECT * FROM product LEFT JOIN product_details
47
ON (product.id
= product_details.id);
48
+----+--------+------+--------+-------+
49
|
id | amount | id| weight | exist |
50
+----+--------+------+--------+-------+
51
|
1 |100 | NULL |NULL |NULL |
52
|
2 |200 |2 |22 |0 |
53
|
3 |300 | NULL |NULL |NULL |
54
|
4 |400 |4 |44 |1 |
55
+----+--------+------+--------+-------+
56
4 rows in set (0.00
sec)
ON 子句和 WHERE 子句有什么不同？
一个问题：下面两个查询的结果集有什么不同么？
1
1. SELECT * FROM product LEFT JOIN product_details
2
ON (product.id
= product_details.id)
3
ANDproduct_details.id=2;
4
2. SELECT * FROM product LEFT JOIN product_details
5
ON (product.id
= product_details.id)
6
WHERE product_details.id=2;
用例子来理解最好不过了：
01
mysql SELECT * FROM product LEFT JOIN product_details
02
ON (product.id
= product_details.id)
03
AND product_details.id=2;
04
+----+--------+------+--------+-------+
05
|
id | amount | id| weight | exist |
06
+----+--------+------+--------+-------+
07
|
1 |100 | NULL |NULL |NULL |
08
|
2 |200 |2 |22 |0 |
09
|
3 |300 | NULL |NULL |NULL |
10
|
4 |400 | NULL |NULL |NULL |
11
+----+--------+------+--------+-------+
12
4 rows in set (0.00
sec)
13
14
mysql SELECT * FROM product LEFT JOIN product_details
15
ON (product.id
= product_details.id)
16
WHERE product_details.id=2;
17
+----+--------+----+--------+-------+
18
|
id | amount | id | weight | exist |
19
+----+--------+----+--------+-------+
20
|
2 |200 |2 |22 |0 |
21
+----+--------+----+--------+-------+
22
1
row in set (0.01
sec)
第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。
第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。
再来看一些示例：
01
mysql
02
mysql SELECT * FROM product LEFT JOIN product_details
03
ON product.id
= product_details.id
04
AND product.amount=100;
05
+----+--------+------+--------+-------+
06
|
id | amount | id| weight | exist |