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