mysql中join，left join，right join 的差异

mysql中join，left join，right join 的区别

先看例子：

首先是join

select vend_name ,prod_price,prod_name from products join vendors on vendors.vend_id = products.vend_id;

结果

+-----------------+------------+---------------------+
| vend_name       | prod_price | prod_name           |
+-----------------+------------+---------------------+
| Bears R Us      | 5.99       | 8 inch teddy bear   |
| Bears R Us      | 8.99       | 12 inch teddy bear  |
| Bears R Us      | 11.99      | 18 inch teddy bear  |
| Doll House Inc. | 3.49       | Fish bean bag toy   |
| Doll House Inc. | 3.49       | Bird bean bag toy   |
| Doll House Inc. | 3.49       | Rabbit bean bag toy |
| Doll House Inc. | 4.99       | Raggedy Ann         |
| Fun and Games   | 9.49       | King doll           |
| Fun and Games   | 9.49       | Queen doll          |
+-----------------+------------+---------------------+

left join

select vend_name ,prod_price,prod_name from products left 
join vendors on vendors.vend_id = products.vend_id;

结果

+-----------------+------------+---------------------+
| vend_name       | prod_price | prod_name           |
+-----------------+------------+---------------------+
| Doll House Inc. | 3.49       | Fish bean bag toy   |
| Doll House Inc. | 3.49       | Bird bean bag toy   |
| Doll House Inc. | 3.49       | Rabbit bean bag toy |
| Bears R Us      | 5.99       | 8 inch teddy bear   |
| Bears R Us      | 8.99       | 12 inch teddy bear  |
| Bears R Us      | 11.99      | 18 inch teddy bear  |
| Doll House Inc. | 4.99       | Raggedy Ann         |
| Fun and Games   | 9.49       | King doll           |
| Fun and Games   | 9.49       | Queen doll          |
+-----------------+------------+---------------------+

right join的情况呢

select vend_name ,prod_price,prod_name from products right
 join vendors on vendors.vend_id = products.vend_id;

+-----------------+------------+---------------------+
| vend_name       | prod_price | prod_name           |
+-----------------+------------+---------------------+
| Bear Emporium   | NULL       | NULL                |
| Bears R Us      | 5.99       | 8 inch teddy bear   |
| Bears R Us      | 8.99       | 12 inch teddy bear  |
| Bears R Us      | 11.99      | 18 inch teddy bear  |
| Doll House Inc. | 3.49       | Fish bean bag toy   |
| Doll House Inc. | 3.49       | Bird bean bag toy   |
| Doll House Inc. | 3.49       | Rabbit bean bag toy |
| Doll House Inc. | 4.99       | Raggedy Ann         |
| Fun and Games   | 9.49       | King doll           |
| Fun and Games   | 9.49       | Queen doll          |
| Furball Inc.    | NULL       | NULL                |
| Jouets et ours  | NULL       | NULL                |
+-----------------+------------+---------------------+

总结：join 的结果会和left join或者right join的其一的结果一样，顺序不同，不知道为什么呢，left join 左联结优先考虑左表，即products ，得到的结果行数和products表的行数一样，right join 右联结，有线考虑右表，即vendors ，得到的结果和右表vendors的行数一样，无法匹配的时候用NULL值填充。