Explain output difference between this inner and natural join

join;oracle

A NATURAL JOIN is a JOIN operation that creates an implicit join
clause for you based on the common columns in the two tables being
joined. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a
RIGHT OUTER join. The default is INNER join. Source

So a natural join can be a shorthand way of implementing inner join if both tables have a common column.

Consider following table:

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

We join the table to itself:

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

Try to do the same with natural join notation:

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

Given that natural join here is implemented using inner join why the multiple rows?

Update:

Running same join with table aliases has different output:

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

Best Answer

Oracle database does not understand ANSI join syntax internally (except FULL OUTER JOIN), it rewrites such queries to its own, old join syntax. If you enable optimizer trace, for example:

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

And run the queries, in the generated trace file, you can view the final form of the query that is executed. 

select id, planet from t1 ta natural join t1 tb

is transformed into (BP is my schema):

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"

And

select id, planet from t1 natural join t1

is transformed into:

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"

And this is the problem, "T1"."PLANET"="T1"."PLANET" AND "T1"."ID"="T1"."ID".

But don't ask me why, it is just one of those "unexplainable" things in Oracle.

I have seen a case, where the transformation resulted in a similarly ambigiuous final query, and there was an actual error message in the trace file, still, the database returned wrong results and no error to the client.

Related Question