Question

mysql where和 join on 哪个效率高

Answer 1

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行，即使on中包含有A表中的列的限制条件，也不会过滤A的任何数据（A的数据只会通过where过滤）。

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据

在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

让我们看一个 LFET JOIN 示例：

01

mysql> CREATE TABLE `product`
(

02

`id` int(10)
unsigned NOT NULL auto_increment,

03

`amount` int(10)
unsigned default NULL,

04

PRIMARY KEY (`id`)

05

)
ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

06

07

mysql> CREATE TABLE `product_details`
(

08

`id` int(10)
unsigned NOT NULL,

09

`weight` int(10)
unsigned default NULL,

10

`exist` int(10)
unsigned default NULL,

11

PRIMARY KEY (`id`)

12

)
ENGINE=MyISAM DEFAULT CHARSET=latin1

13

14

mysql> INSERT INTO product
(id,amount)

15

VALUES (1,100),(2,200),(3,300),(4,400);

16

Query
OK, 4 rows affected
(0.00 sec)

17

Records:
4 Duplicates: 0 Warnings: 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:
4 Duplicates: 0 Warnings: 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

AND product_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 |

07

+----+--------+------+--------+-------+

08

|
1 | 100 | NULL | NULL | NULL |

09

|
2 | 200 | NULL | NULL | NULL |

10

|
3 | 300 | NULL | NULL | NULL |

11

|
4 | 400 | NULL | NULL | NULL |

12

+----+--------+------+--------+-------+

13

4 rows in set (0.00
sec)

所有来自product表的数据行都被检索到了，但没有在product_details表中匹配到记录（product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据）

01

mysql> SELECT * FROM product LEFT JOIN product_details

02

ON (product.id
= product_details.id)

03

AND product.amount=200;

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.01
sec)

同样，所有来自product表的数据行都被检索到了，有一条数据匹配到了。