3

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   
*/
  • This table also has PLANET as a common column, is that causing the duplicates? – Mayberg May 8 '17 at 11:11
  • 1
    Does it make a difference if you do: SELECT first.id, first.planet FROM t1 first NATURAL JOIN t1 second? – Lennart May 8 '17 at 11:35
  • 2
    @Kshitiz Sharma, beats me. Looks like a bug – Lennart May 8 '17 at 11:43
  • 4
    I would forget that Natural Join even exists at all (unless your studying for one of the Oracle SQL Certifications, LOL). From Tom Kyte: "the natural join syntax is stupid and error prone. It does, it can, it has caused many a mistake. there is a zero percent chance I would ever use it.": asktom.oracle.com/pls/asktom/… – Kris Johnston May 8 '17 at 13:06
  • 1
    According to this MOS Document-Wrong Result with ANSI Using Join of Tables with Identical Aliases (Doc ID 2229061.1), there is one related bug which they are still working on. Workaround is to use different table aliases. – JSapkota May 8 '17 at 17:12
6

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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.