PostgreSQL Multiple USING clause

user_13323712 0
Viewed 1094

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.

using postgresql sql
3 Answers

If you use USING, you will have only a single userid in the join result, so there will be no ambiguity.

All the documentation has to say is

Furthermore, the output of JOIN USING suppresses redundant columns:

but it also refers to column references. You can use userid without a table qualification and you won't get an "ambiguous column reference" error, because PostgreSQL knows that the column exists only once.

user_64643080

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.

user_11440350

You can explain the plan and check:

CREATE TABLE a (
  userid INT
, nm VARCHAR(32)
);
CREATE TABLE b (
  userid INT
, adr VARCHAR(32)
);
CREATE TABLE c (
  userid INT
, car VARCHAR(32)
);

EXPLAIN
SELECT *
FROM a
JOIN b USING(userid)
JOIN c USING(userid)
;
-- out                               QUERY PLAN                               
-- out -----------------------------------------------------------------------
-- out  Merge Join  (cost=152.17..334.01 rows=8946 width=250)
-- out    Merge Cond: (a.userid = c.userid)
-- out    ->  Merge Join  (cost=101.45..142.80 rows=2520 width=172)
-- out          Merge Cond: (a.userid = b.userid)
-- out          ->  Sort  (cost=50.72..52.50 rows=710 width=86)
-- out                Sort Key: a.userid
-- out                ->  Seq Scan on a  (cost=0.00..17.10 rows=710 width=86)
-- out          ->  Sort  (cost=50.72..52.50 rows=710 width=86)
-- out                Sort Key: b.userid
-- out                ->  Seq Scan on b  (cost=0.00..17.10 rows=710 width=86)
-- out    ->  Sort  (cost=50.72..52.50 rows=710 width=86)
-- out          Sort Key: c.userid
-- out          ->  Seq Scan on c  (cost=0.00..17.10 rows=710 width=86)

-- and with a full outer join:

EXPLAIN
SELECT *
FROM a
FULL JOIN b USING(userid)
FULL JOIN c USING(userid)
;
-- out                                  QUERY PLAN                                  
-- out -----------------------------------------------------------------------------
-- out  Merge Full Join  (cost=326.16..463.90 rows=8946 width=250)
-- out    Merge Cond: (c.userid = (COALESCE(a.userid, b.userid)))
-- out    ->  Sort  (cost=50.72..52.50 rows=710 width=86)
-- out          Sort Key: c.userid
-- out          ->  Seq Scan on c  (cost=0.00..17.10 rows=710 width=86)
-- out    ->  Sort  (cost=275.43..281.73 rows=2520 width=172)
-- out          Sort Key: (COALESCE(a.userid, b.userid))
-- out          ->  Hash Full Join  (cost=25.98..133.06 rows=2520 width=172)
-- out                Hash Cond: (a.userid = b.userid)
-- out                ->  Seq Scan on a  (cost=0.00..17.10 rows=710 width=86)
-- out                ->  Hash  (cost=17.10..17.10 rows=710 width=86)
-- out                      ->  Seq Scan on b  (cost=0.00..17.10 rows=710 width=86)
user_67882550
Related