mysql数据库join、left join与right join 的区别

原创

LecKey 2023-04-25 09:16:25 博主文章分类：MySQL ©著作权

文章标签 mysql数据库joinleft joi 文章分类 jQuery 前端开发

©著作权归作者所有：来自51CTO博客作者LecKey的原创作品，请联系作者获取转载授权，否则将追究法律责任

首先是join

1. select vend_name ,prod_price,prod_name from products join vendors on

结果

1. +-----------------+------------+---------------------+
2. | vend_name       | prod_price | prod_name           |  
3. +-----------------+------------+---------------------+
4. | Bears R Us      | 5.99       | 8 inch teddy bear   |  
5. | Bears R Us      | 8.99       | 12 inch teddy bear  |  
6. | Bears R Us      | 11.99      | 18 inch teddy bear  |  
7. | Doll House Inc. | 3.49       | Fish bean bag toy   |  
8. | Doll House Inc. | 3.49       | Bird bean bag toy   |  
9. | Doll House Inc. | 3.49       | Rabbit bean bag toy |  
10. | Doll House Inc. | 4.99       | Raggedy Ann         |  
11. | Fun and
12. | Fun and
13. +-----------------+------------+---------------------+

left join

1. select vend_name ,prod_price,prod_name from products left
2. join vendors on

结果

1. +-----------------+------------+---------------------+
2. | vend_name       | prod_price | prod_name           |  
3. +-----------------+------------+---------------------+
4. | Doll House Inc. | 3.49       | Fish bean bag toy   |  
5. | Doll House Inc. | 3.49       | Bird bean bag toy   |  
6. | Doll House Inc. | 3.49       | Rabbit bean bag toy |  
7. | Bears R Us      | 5.99       | 8 inch teddy bear   |  
8. | Bears R Us      | 8.99       | 12 inch teddy bear  |  
9. | Bears R Us      | 11.99      | 18 inch teddy bear  |  
10. | Doll House Inc. | 4.99       | Raggedy Ann         |  
11. | Fun and
12. | Fun and
13. +-----------------+------------+---------------------+

right join的情况呢

1. select vend_name ,prod_price,prod_name from products right
2. join vendors on

1. +-----------------+------------+---------------------+
2. | vend_name       | prod_price | prod_name           |  
3. +-----------------+------------+---------------------+
4. | Bear Emporium   | NULL       | NULL
5. | Bears R Us      | 5.99       | 8 inch teddy bear   |  
6. | Bears R Us      | 8.99       | 12 inch teddy bear  |  
7. | Bears R Us      | 11.99      | 18 inch teddy bear  |  
8. | Doll House Inc. | 3.49       | Fish bean bag toy   |  
9. | Doll House Inc. | 3.49       | Bird bean bag toy   |  
10. | Doll House Inc. | 3.49       | Rabbit bean bag toy |  
11. | Doll House Inc. | 4.99       | Raggedy Ann         |  
12. | Fun and
13. | Fun and
14. | Furball Inc.    | NULL       | NULL
15. | Jouets et ours  | NULL       | NULL
16. +-----------------+------------+---------------------+

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