自然连接 natural join

最新推荐文章于 2022-05-12 05:15:00 发布

huangliang0703

最新推荐文章于 2022-05-12 05:15:00 发布

阅读量777

点赞数

CC 4.0 BY-SA版权

分类专栏： 0cp-047 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/huangliang0703/article/details/36383233

0cp-047 专栏收录该内容

12 篇文章

订阅专栏

探讨了SQL中自然连接(NATURAL JOIN)的正确使用方法，并通过具体例子说明了WHERE子句的位置对于查询结果的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

74. View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS
tables.
You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price
(UNIT_PRICE multiplied by QUANTITY), where the total price is greater than
50,000.
You executed the following SQL statement:
SELECT order_id, product_id, unit_price*quantity "Total Price"
FROM order_items
WHERE unit_price*quantity > 50000 NATURAL JOIN orders;

Which statement is true regarding the execution of the statement?

A. The statement would execute and provide the desired result.
B. The statement would not execute because the ON keyword is missing in the
NATURAL JOIN clause.
C. The statement would not execute because the WHERE clause is before the
NATURAL JOIN clause.
D. The statement would not execute because the USING keyword ismissing in
the NATURAL JOIN
clause.
Answer: C

自然连接（NATURAL JOIN）是一种特殊的等价连接，它将表中具有相同名称的列
自动进行记录匹配。自然连接不必指定任何同等连接条件。

下面举例论证：

SQL> select deptno from scott.dept;

    DEPTNO
----------
        10
        20
        30
        40

SQL> select deptno from scott.emp;

    DEPTNO
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20

    DEPTNO
----------
        30
        20
        10

14 rows selected.

SQL> select deptno,ename from scott.dept natural join scott.emp where deptno>30; 

no rows selected <span style="color:#ff0000;">//先对两个表做自然连接，对连接结果在做deptno>30的条件输出</span>



SQL> select deptno,ename from scott.dept  where deptno>30 natural join scott.emp;
select deptno,ename from scott.dept  where deptno>30 natural join scott.emp
                                                     *
ERROR at line 1:
ORA-00933: SQL command not properly ended