OCP-1Z0-051-V9.02-120题

120. View the Exhibits  and examine the structures of the PRODUCTS,  SALES,  and CUSTOMERS
tables.
You issue the following query:
SQL>SELECT p.prod_id,prod_name,prod_list_price,           
quantity_sold,cust_last_name
FROM products p NATURAL JOIN sales s NATURAL JOIN customers c
WHERE prod_id =148;
Which statement is true regarding the outcome of this query?
A. It executes successfully.
B. It produces an error because the NATURAL join can be used only with two tables.
C. It produces an error because a  column used in the  NATURAL join cannot have a qualifier.

D. It produces an error because all columns used in the NATURAL join should have a qualifier.
Answer: C

 

sh@TEST0910> SELECT p.prod_id,prod_name,prod_list_price,quantity_sold,cust_last_name

  2  FROM products p NATURAL JOIN sales s NATURAL JOIN customers c

  3  WHERE prod_id =148 and rownum<6;

SELECT p.prod_id,prod_name,prod_list_price,quantity_sold,cust_last_name

       *

ERROR at line 1:

ORA-25155: column used in NATURAL join cannot have qualifier

 

列用于自然连接不能有限定符。

 

去掉限定符后：

sh@TEST0910> SELECT prod_id,prod_name,prod_list_price,quantity_sold,cust_last_name

  2   FROM products p NATURAL JOIN sales s NATURAL JOIN customers c

  3  WHERE prod_id =148 and rownum<6;

 

   PROD_ID PROD_NAME                      PROD_LIST_PRICE QUANTITY_SOLD CUST_LAST_NAME

---------- ------------------------------ --------------- ------------- ------------------------------

       148 Xtend Memory                             20.99             1 Llyles

       148 Xtend Memory                             20.99             1 Lake

       148 Xtend Memory                             20.99             1 Koch

       148 Xtend Memory                             20.99             1 Skillman

       148 Xtend Memory                             20.99             1 Speer

 

列不用于自然连接，可以使用限定符。

 

sh@TEST0910>  SELECT prod_id, p.prod_name,prod_list_price,quantity_sold,cust_last_name

  2  FROM products p NATURAL JOIN sales s NATURAL JOIN customers c

  3  WHERE prod_id =148 and rownum<6;

 

   PROD_ID PROD_NAME                      PROD_LIST_PRICE QUANTITY_SOLD CUST_LAST_NAME

---------- ------------------------------ --------------- ------------- ------------------------------

       148 Xtend Memory                             20.99             1 Llyles

       148 Xtend Memory                             20.99             1 Lake

       148 Xtend Memory                             20.99             1 Koch

       148 Xtend Memory                             20.99             1 Skillman

       148 Xtend Memory                             20.99             1 Speer

 

自然连接:

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

 

来看一下自然连接的例子。

Select emp.ename,dept.dname

From emp natural join dept;

这里我们并没有指定连接的条件，实际上oracle为我们自作主张的将，emp中的deptno和dept中的deptno做了连接。

也就是实际上相当于

Select emp.ename,dept.dname

From emp join dept on emp.deptno = dept.deptno;

因为这两张表的这两个字段deptno的类型个名称完全相同。所以使用natural join时被自然的连接在一起了。

另外：

1.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为自然连接的条件。

2.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。

3.由于oracle中可以进行这种非常简单的natural join，我们在设计表时，应该尽量在不同表中具有相同含义的字段使用相同的名字和数据类型。以方便以后使用natural join。