NATURAL JOIN

NATURAL JOIN The NATURAL keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

On occasion, the table pairings in natural or cross joins may be ambiguous. For example:

a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1

can be interpreted in either of the following ways:

a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1)    (a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1

To avoid this ambiguity, you can use parentheses to specify the pairings of joined tables. In the absence of such parentheses, the database uses left associativity, pairing the tables from left to right.

Restriction on Natural Joins You cannot specify a LOB column, columns of ANYTYPE, ANYDATA, or ANYDATASET, or a collection column as part of a natural join.

1.如果做自然连接的两个表的有多个字段都满足有相同名称个类型，那么他们会被作为自然连
接的条件。
2.如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。
3.由于oracle中可以进行这种非常简单的natural join，我们在设计表时，应该尽量在不同表
中具有相同含义的字段使用相同的名字和数据类型。以方便以后使用natural join

89. View the Exhibit and examine the description of the DEPARTMENTS and
EMPLOYEES tables.To retrieve data for all the employees for their EMPLOYEE_ID,
FIRST_NAME, and DEPARTMENT NAME,the following SQL statement was written:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement.
What could be the reason for this?
A. The NATURAL JOIN clause is missing the USING clause.
B. The table prefix is missing for the column names in the SELECT clause.
C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM
clause.
D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the
same column name and data type.
Answer: D
Obtained: