先看例子:
首先是join
结果
left join
结果
right join的情况呢
总结:join 的结果会和left join或者right join的其一的结果一样,顺序不同,不知道为什么呢,left join 左联结优先考虑左表,即products ,得到的结果行数和products表的行数一样,right join 右联结,有线考虑右表,即vendors ,得到的结果和右表vendors的行数一样,无法匹配的时候用NULL值填充。
首先是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值填充。