How NATURAL JOIN works with two tables when there are two or more common columns?

1
votes

How NATURAL JOIN works with two tables when there are two or more common columns? For example if i have tableA with columns A.A, A.B and A.C and tableB with columns B.A, B.B and B.D and I type: select * from A NATURAL JOIN B, which will be the column of the NATURAL JOIN, A or B (they are both common)?

1
sql
What happened when you tried? (and besides: this is one of the reasons why natural join should not be used) - a_horse_with_no_name

1 Answers

1
votes

From ISO/IEC 9075-2:1999 (E) section 7.7 :

If NATURAL is specified, then let common column name be a <column name> that is the <column name> of exactly one column of T1 and the <column name> of exactly one column of T2. [...]Let corresponding join columns refer to all columns of T1 and T2 that have common column names, if any.

[...]

If NATURAL is specified or <named columns join> is specified, then

Case:

i) If there are corresponding join columns, then let T be the multiset of rows of CP for which the corresponding join columns have equal values.

So according to the standard if there are two common column-names the join will be based on all two columns.