OCP-1Z0-051 第131题 LEFT OUTER JOIN和RIGHT OUTER JOIN的用法

一、原题

View the Exhibit and examine the structure of the PRODUCT, COMPONENT, and PDT_COMP tables.

In PRODUCT table, PDTNO is the primary key.
In COMPONENT table, COMPNO is the primary key.
In PDT_COMP table, (PDTNO,COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT table.
You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist.
Evaluate the following query:
SQL>SELECT pdtno,pdtname,compno,compname
            FROM product _____________ pdt_comp
           USING (pdtno) ____________ component USING(compno)
          WHERE compname IS NOT NULL;
Which combination of joins used in the blanks in the above query gives the correct output?
A. JOIN; JOIN
B. FULL OUTER JOIN; FULL OUTER JOIN
C. RIGHT OUTER JOIN; LEFT OUTER JOIN
D. LEFT OUTER JOIN; RIGHT OUTER JOIN

答案：C

二、题目翻译

查看 PRODUCT、COMPONENT和PDT_COMP表的结构：
PDTNO是表PRODUCT的主键
COMPNO是表COMPONENT的主键
PDTNO,COMPNO是表PDT_COMP的组合主键，PDTNO是关联PRODUCT表的外键，COMPNO是关联COMPONENT表的外键
要生成一个报表，如果component names和product names存在，则显示product names和对应的component names。
评估下面的语句：
哪一个join组合填在下面的空格处能得出正确的结果?

三、题目解析

现在题目的意思是，要根据PDT_COMP表中的数据，来关联PRODUCT、 COMPONENT两张表，
如果PDT_COMP表中存在的数据，能匹配上PRODUCT、 COMPONENT表中的数据，则显示，
所以，第一次关联的时候，以右表PDT_COMP为基准，右连接，再以关联后的结果为基准，左连接，然后再去掉null值。

表连接的用法，详见：
    http://blog.csdn.net/holly2008/article/details/25704471