主页 / dba / 问题 / 172954
Accepted
Kshitiz Sharma
Asked: 2017-05-09 02:57:12 +0800 CST

解释此内部连接和自然连接之间的输出差异

  • 772

NATURAL JOIN 是一种 JOIN 操作，它根据要连接的两个表中的公共列为您创建隐式连接子句。NATURAL JOIN 可以是 INNER 联接、LEFT OUTER 联接或 RIGHT OUTER 联接。默认是 INNER 连接。资源

因此，如果两个表都有一个公共列，自然连接可以是实现内部连接的一种简写方式。

考虑下表：

SELECT * FROM t1;
/*
        ID PLANET  
---------- --------
         1 jupiter 
         2 earth 
*/

我们将表连接到自身：

SELECT first.id, first.planet FROM t1 first INNER JOIN t1 second ON first.id=second.id;
/*
        ID PLANET  
---------- --------
         1 jupiter 
         2 earth   
*/

尝试用自然连接符号做同样的事情：

SELECT id, planet FROM t1 NATURAL JOIN t1;
/*
        ID PLANET  
---------- --------
         1 jupiter 
         1 jupiter 
         2 earth   
         2 earth   
*/

鉴于这里的自然连接是使用内部连接实现的，为什么多行？

更新：

使用表别名运行相同的连接有不同的输出：

SELECT id, planet FROM t1 first NATURAL JOIN t1 second;
/*
        ID PLANET  
---------- --------
         1 jupiter 
         2 earth   
*/
oracle join

2 个回答

  1. Best Answer

    Oracle 数据库内部不理解 ANSI 连接语法（除了FULL OUTER JOIN），它将此类查询重写为它自己的旧连接语法。如果启用优化器跟踪，例如：

    alter session set events '10053 trace name context forever, level 1';

    并运行查询，在生成的跟踪文件中，您可以查看执行的查询的最终形式。

    select id, planet from t1 ta natural join t1 tb

    转化为（BP是我的模式）：

    Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TB"."ID" "ID","TB"."PLANET" "PLANET" FROM "BP"."T1" "TA","BP"."T1" "TB"
WHERE "TA"."PLANET"="TB"."PLANET" AND "TA"."ID"="TB"."ID"

    和

    select id, planet from t1 natural join t1

    被转化为：

    Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."PLANET" "PLANET" FROM "BP"."T1" "T1","BP"."T1" "T1"
WHERE "T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID"

    这就是问题所在"T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID"。

    但是不要问我为什么，这只是 Oracle 中那些“无法解释”的事情之一。

    我见过一个案例，其中转换导致类似的模棱两可的最终查询，并且在跟踪文件中有一条实际的错误消息，但数据库仍然返回了错误的结果并且没有向客户端返回错误。

    • 6

  2. 看起来像一个错误。

    首先，selfx NATURAL JOIN x应该返回与原始表相同的行数和列数。因此，返回 2 行的最后一个查询是正确的。返回 4 行的第二个查询似乎正在执行CROSS JOIN（如果您尝试使用 3 行的表，它返回 9）。

    其次，查询应该给出"ORA-00918: column ambiguously defined"错误或类似的东西，因为不允许在同一FROM个子句中有 2 个具有相同别名的表。

    请参阅dbfiddle.uk上的测试，其中交叉连接返回此错误消息：

    > SELECT id, planet FROM t1, t1;
> ORA-00918: column ambiguously defined

> SELECT id, planet FROM t1 CROSS JOIN t1;
> ORA-00918: column ambiguously defined

    以及在 Postgres 中测试的相同查询：

    > SELECT * FROM t1 NATURAL JOIN t1;
> ERROR:  table name "t1" specified more than once
    • 3

相关问题