Explain output difference between this inner and natural join

Question

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   
*/

Answer 1

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.

Answer 2

Looks like a bug.

First, a self x NATURAL JOIN x should return the same number of rows and columns as the original table. So, the last query that returns 2 rows is correct. The 2nd query, that returns 4 rows, seems to be doing a CROSS JOIN instead (if you try with a table of 3 rows, it returns 9).

Second, the query should have given a "ORA-00918: column ambiguously defined" error or something similar because it is not allowed to have 2 tables with same alias in the same FROM clause.

See the tests at dbfiddle.uk, where cross joins return this error message:

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

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

and the same queries tested in Postgres:

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