What is the best way to achieve a transitive join in postgresql? Currently, I want to do a full outer join for tables, a, b, and c. My query currently looks like this:
SELECT *
FROM a
FULL OUTER JOIN b
ON a."ID" = b."ID"
FULL OUTER JOIN c
ON a."ID" = c."ID"
I'm running into an issue where some records that match in table B and table C are showing up in different rows in the query output, and I realized that it must be because I have not explicitly joined tables B and C. What is the best way to write a "transitive" query where a=b, a=c, and b=c?
Here is an example of my current output. Right now, when a matching ID exists for just tables B and C, I get 2 different rows:
A ID | B ID | C ID |
---|---|---|
32 | 32 | null |
35 | 35 | 35 |
36 | null | 36 |
null | 42 | null |
null | null | 42 |
Here is my desired output:
A ID | B ID | C ID |
---|---|---|
32 | 32 | null |
35 | 35 | 35 |
36 | null | 36 |
null | 42 | 42 |
Use using
:
SELECT *
FROM a FULL OUTER JOIN
b
USING ("ID") FULL OUTER JOIN
c
USING ("ID");
If in your real example, the columns have different names:
SELECT *
FROM a FULL OUTER JOIN
b
ON b.id = a.id FULL OUTER JOIN
c
ON c.id = COALESCE(b.id, a.id);