OCP-1Z0-051 第127题 LEFT JOIN,RIGHT JOIN,FULL JOIN外连接的用法

一、原题

Examine the data in the CUSTOMERS table:
CUSTNO CUSTNAME CITY
     1 KING S   EATTLE
     2 GREEN    BOSTON
     3 KOCHAR   SEATTLE
     4 SMITH    NEW YORK
You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
SQL>SELECT c1.custname, c1.city
            FROM Customers c1 __________________ Customers c2
                 ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
A. JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. FULL OUTER JOIN
E. RIGHT OUTER JOIN

答案：AE

二、题目翻译

查看CUSTOMERS表的数据：
列出多于一个客户的城市和客户的信息。
评估下面的查询
哪两种JOIN用在上面的空白处能给出正确的结果？

三、题目解析

B选项不正确，NATURAL JOIN自然连接不需要关联条件，所以下面的ON子句会报错。
CD选项不正确，LEFT OUTER JOIN和FULL OUTER JOIN都会显示有客户但是没在某个城市的人。

四、测试
         测试结果，A，E正确。

SQL> Create table customers (CUSTNO NUMBER,CUSTNAME VARCHAR2(20),CITY VARCHAR2(20));

Table created.

SQL> insert into customers values(1,'KING','SEATTLE');

1 row created.

SQL> insert into customers values(2,'GREEN','BOSTON');

1 row created.

SQL> insert into customers values(3,'KOCHAR','SEATTLE');

1 row created.

SQL> insert into customers values(4,'SMITH','NEY YORK');

1 row created.

SQL> COMMIT;

Commit complete.

--题目要求客户多于一个的城市，先看一下，有哪些城市，查询结果，只有SEAITLE这个城市。
SQL> select CITY from customers
  2  group by city
  3  having count(CUSTNAME)>1
  4  order by city;CITY
---------------------------------
SEATTLE

SQL> select c1.custname, c1.city
  2  from Customers c1 JOIN Customers c2
  3  ON (c1.city=c2.city AND c1.custname<>c2.custname);

CUSTNAME                                 CITY
---------------------------------------- ----------------------------------------
KOCHAR                                   SEATTLE
KING                                     SEATTLE

SQL> select c1.custname, c1.city
  2  from Customers c1 NATURAL JOIN Customers c2
  3  ON (c1.city=c2.city AND c1.custname<>c2.custname);
select c1.custname, c1.city
from Customers c1 NATURAL JOIN Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> select c1.custname, c1.city 
  2  from Customers c1 LEFT OUTER JOIN Customers c2
  3  ON (c2.city=c1.city AND c2.custname<>c1.custname);

CUSTNAME                                 CITY
---------------------------------------- -----------
KOCHAR                                   SEATTLE
KING                                     SEATTLE
GREEN                                    BOSTON

SQL> select c1.custname, c1.city
  2  from Customers c1 FULL OUTER JOIN Customers c2
  3  ON (c2.city=c1.city AND c2.custname<>c1.custname);

CUSTNAME                                 CITY
---------------------------------------- -----------
KOCHAR                                   SEATTLE
KING                                     SEATTLE
GREEN                                    BOSTON

SQL> select c1.custname, c1.city
  2  from Customers c1 RIGHT OUTER JOIN Customers c2
  3  ON (c2.city=c1.city AND c2.custname<>c1.custname);

CUSTNAME                                 CITY
---------------------------------------- ----------------------------------------
KING                                     SEATTLE
KOCHAR                                   SEATTLE

 

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