OCP-1Z0-051-2015-44题

QUESTION NO: 44

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:

You issue the following query:

Which statement is true regarding the outcome of this query?
A. It produces an error because the NATURAL join can be used only with two tables
B. It produces an error because a column used in the NATURAL join cannot have a qualifier
C. It produces an error because all columns used in the NATURAL join should have a qualifier

D. It executes successfully

Answer: B
Explanation:
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. 

The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: 自然连接用using将相同列连接，格式如下
SELECT table1.column, table2.column
FROM table1
JOIN table2 USING (join_column1, join_column2…);
While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax
does not.
An error is raised if the keywords NATURAL and USING occur in the same join clause. The
JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets
after the USING keyword. This avoids the shortcomings associated with the pure natural join.
Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

题目给出的语句有问题：列用于自然连接不能有限定符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;

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

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

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

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