CODEWITHSUNDEEP
sql
ppetyr
ppetyr

Reputation: 111

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

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)?

Upvotes: 1

Views: 1911

Answers (1)

piet.t
piet.t

Reputation: 11911

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.

Upvotes: 2

Related Questions