mysql - 不确定这个右外连接的结果是什么

Question

右外连接类似于维恩图的 Union 对吧？
我的意思是，对于 A right outer Join B，我们应该获取 B 的所有行以及 A 中的所有匹配行。
出于某种原因，我对以下内容感到困惑:
假设表 Orders:

mysql> select * from orders;  
+------------+------------+---------+----------+---------+  
| orderedon  | name       | partnum | quantity | remarks |  
+------------+------------+---------+----------+---------+  
| 1996-05-19 | TRUE-WHEEL |      76 |        3 | PAID    |  
| 1996-09-02 | TRUE-WHEEL |      10 |        1 | PAID    |   
| 1996-06-30 | TRUE-WHEEL |      42 |        8 | PAID    |   
| 1996-06-30 | BIKE SPEC  |      54 |       10 | PAID    |  
| 1996-05-30 | BIKE SPEC  |      23 |        8 | PAID    |   
| 1996-01-17 | BIKE SPEC  |      76 |       11 | PAID    |  
| 1996-01-17 | LE SHOPPE  |      76 |        5 | PAID    |  
| 1996-06-01 | LE SHOPPE  |      10 |        3 | PAID    |  
| 1996-06-01 | AAA BIKE   |      10 |        1 | PAID    |   
| 1996-07-01 | AAA BIKE   |      76 |        4 | PAID    |  
| 1996-07-01 | AAA BIKE   |      46 |       14 | PAID    |   
| 1996-07-11 | JACKS BIKE |      76 |       14 | PAID    |  
| 1996-05-15 | TRUE-WHEEL |      23 |        6 | PAID    |  
| 1996-05-30 | BIKE SPEC  |      20 |        2 | PAID    |   
+------------+------------+---------+----------+---------+   
14 rows in set (0.00 sec)

和表部分:

mysql> select * from part;  
+---------+---------------+---------+  
| partnum | description   | price   |  
+---------+---------------+---------+  
|      54 | PEDALS        |   54.25 |  
|      42 | SEATS         |   24.50 |  
|      46 | TIRES         |   15.25 |  
|      23 | MOUNTAIN BIKE |  350.45 |  
|      76 | ROAD BIKE     |  530.00 |  
|      10 | TANDEM        | 1200.00 |  
+---------+---------------+---------+  
6 rows in set (0.00 sec)  

我期待以下查询:
select p.partnum p_partnum,p.description p_desc,p.price p_price,o.name o_name,o.partnum o_partnum from part p right outer join orders o on o.partnum=54;
会给我 Orders 的所有行以及 part 的 partnum=54 的行。
但我明白了:

mysql> select p.partnum p_partnum,p.description p_desc,p.price p_price,o.name   o_name,o.partnum o_partnum from part p right outer join orders o on o.partnum=54;   
+-----------+---------------+---------+------------+-----------+  
| p_partnum | p_desc        | p_price | o_name     | o_partnum |  
+-----------+---------------+---------+------------+-----------+  
|      NULL | NULL          |    NULL | TRUE-WHEEL |        76 |  
|      NULL | NULL          |    NULL | TRUE-WHEEL |        10 |   
|      NULL | NULL          |    NULL | TRUE-WHEEL |        42 |   
|        54 | PEDALS        |   54.25 | BIKE SPEC  |        54 |   
|        42 | SEATS         |   24.50 | BIKE SPEC  |        54 |   
|        46 | TIRES         |   15.25 | BIKE SPEC  |        54 |   
|        23 | MOUNTAIN BIKE |  350.45 | BIKE SPEC  |        54 |    
|        76 | ROAD BIKE     |  530.00 | BIKE SPEC  |        54 |   
|        10 | TANDEM        | 1200.00 | BIKE SPEC  |        54 |   
|      NULL | NULL          |    NULL | BIKE SPEC  |        23 |    
|      NULL | NULL          |    NULL | BIKE SPEC  |        76 |    
|      NULL | NULL          |    NULL | LE SHOPPE  |        76 |    
|      NULL | NULL          |    NULL | LE SHOPPE  |        10 |    
|      NULL | NULL          |    NULL | AAA BIKE   |        10 |    
|      NULL | NULL          |    NULL | AAA BIKE   |        76 |    
|      NULL | NULL          |    NULL | AAA BIKE   |        46 |    
|      NULL | NULL          |    NULL | JACKS BIKE |        76 |   
|      NULL | NULL          |    NULL | TRUE-WHEEL |        23 |    
|      NULL | NULL          |    NULL | BIKE SPEC  |        20 |    
+-----------+---------------+---------+------------+-----------+   
19 rows in set (0.00 sec)   

为什么我会得到额外的行？为什么它会将 Order 行与 partnum=54 合并到 `part 的所有行？

Answer 1

您的查询是

select p.partnum p_partnum,p.description p_desc,p.price p_price,o.name   o_name,o.partnum o_partnum 
from part p right outer join orders o on o.partnum=54;  

您要进行右连接，因此即使右侧的连接表中没有匹配项，它也会显示记录，并且在您的查询中会发生这种情况

同样是左连接，即使不匹配，也会显示左侧表的所有记录

引用http://www.w3schools.com/sql/sql_join_right.asp

详细解释

希望对你有帮助