CODEWITHSUNDEEP
sqlpostgresqljoin
planetp
planetp

Reputation: 16085

Why is the column reference ambiguous in INNER JOIN, but not in NATURAL JOIN?

I'm wondering why PostgreSQL produces an ambiguous reference error on this statement:

SELECT c from A JOIN B ON A.c = B.c; -- ERROR:  column reference is ambiguous

but not this:

SELECT c FROM A NATURAL JOIN B; -- OK

In both cases A.c must be identical to B.c.

Upvotes: 3

Views: 3225

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656962

There is one column named "c" in the result of the join in your second query.
There are two columns named "c" in the result of the join in your first query.

Hence, the column name "c" is ambiguous in your first example, but not in your second. The error message is clear. That's the extent of it. It's irrelevant whether the value of both columns named "c" might be identical or not. The reference is ambiguous, hence the error message.

SQL allows multiple columns to have the same name in a result set. But references must be unambiguous. A table-qualification would make it unambiguous:

SELECT a.c FROM a JOIN b ON a.c = b.c;

Or:

SELECT b.c FROM a JOIN b ON a.c = b.c;

Why only one column named "c" in the second query? Quoting the manual:

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

And:

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

Bold emphasis mine.

Upvotes: 0

Patrick
Patrick

Reputation: 32244

In a NATURAL JOIN the fields are joined by name of the column, so if rows match then A.c = B.c by definition. The same is true when using the USING phrase: the match is made on the plain vanilla column names only. The difference between the two is that with the first option all matching column names are joined upon, while with the second option you must specify the like column names and you can thus be selective in which ones to include in the join.

In the case of joining with the ON phrase this equality of row values is not necessarily the case, even if the column names are the same. Consider this scenario:

SELECT c
FROM A
JOIN B ON A.c = 6 * B.c;

In other words, even if both relations have a same-named column, the values of those columns in rows from the two relations do not have to be the same to make a match because any kind of expression may be involved on the values of the either or both of the rows. Hence why you need to be explicit about which relation you want to select the row value from.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

If you want an unambiguous column reference in an explicit join use using:

select c 
from 
    a 
    inner join
    b using (c)

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

Upvotes: 2

Related Questions