PostgreSQL OUTER join logic

Question

I have a pretty old informix procedure which I would like to use in my new PostgreSQL db. I'm new to Postgres and I feel like the joins are pretty different.

This is a part of my old Informix code:

CREATE PROCEDURE mw_getsvid(bid INT)
RETURNING INT;  

DEFINE aid INT;
SELECT a.id INTO aid
    FROM cerberus c, delphi d,
        OUTER (emilia e, fragile f)
    WHERE c.id = [...]

    RETURN aid;

END PROCEDURE;

so what I am trying to do is that c outer joins with e or f. or d outer joins with e or f.

I would be pretty happy if anyone could send me his ideas or a simillar example.

Please read [ask] and [mcve] and google your question before asking. — philipxy, Dec 28 '17 at 07:04

score 2 · Accepted Answer · answered Dec 21 '17 at 08:20

You'll have to use the SQL standard join syntax with PostgreSQL:

FROM cerberus c
   JOIN delphi d ON d.col1 = c.col2
   LEFT JOIN emilia e ON e.col3 = c.col4
   LEFT JOIN fragile f ON f.col5 = c.col6 AND f.col7 = d.col8

Joins are left associative, but you can use parentheses to override that. Of course the order in which you write doen joins is not necessarily the order in which they are executed.

See the documentation for details. This answer also has interesting information.

PostgreSQL OUTER join logic

1 Answers1