I often find myself using (no pun intended) the USING clause in PostgreSQL, and was wondering how the choice would be made in case of multiple USING on the same key name.
Let me explain with a simple example : Let table A, B and C having the userid column. If I am doing :
SELECT A.c1, A.c2, ...
FROM A
JOIN B USING(userid)
JOIN C USING(userid) ;
How does Postrges manages the conflict emerging from the fact that both tables B and C have the column userid ? What is the output resulting from ?
A.userid = B.userid AND A.userid = C.userid
or
A.userid = B.userid AND B.userid = C.userid
I hope I have been clear enough, thank you in advance for your answer.
PS : I am not doing multiple USING clause because I find that unreadable, but I was wondering how it worked.
With inner joins, this query:
SELECT A.c1, A.c2, ...
FROM A JOIN
B
USING (userid) JOIN
C
USING (userid) ;
Is equivalent to:
SELECT A.c1, A.c2, ...
FROM A JOIN
B
ON B.userid = A.userid JOIN
C
ON C.userid = A.userid ; -- or "C.userid = B.userid", it doesn't matter
Note that NULL
values fail the comparison for an INNER JOIN
, so they are not an issue because they are not in the result set.
For LEFT OUTER JOIN
, the logic would be the same as above. For a FULL JOIN
:
SELECT A.c1, A.c2, ...
FROM A FULL JOIN
B
ON B.userid = A.userid FULL JOIN
C
ON C.userid = COALESCE(A.userid, B.userid) ;
Note that throughout the rest of the query userid
, with no qualification, refers to the non-NULL
value from whatever table.