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值填充。

查看更多 下一篇