sqlpostgresqlouter-jointransitivity

Transitive joins in postgresql


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

Solution

  • 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);