Oracle OUTER JOIN (+) in a row - PostgreSQL Migration

Question

I am moving the client software database from Oracle to PostgreSQL, and I have some problems understanding the query, what it does and therefore how to port it.

Inquiry:

SELECT * FROM TBL1, TBL2, TBL3, TBL4 WHERE TBL3.Project_ID = TBL1.Project_ID AND TBL2.Type_ID = TBL1.Type_ID AND TBL4.PROPERTY_NAME(+)='Id' AND TBL4.Entity_ID(+)=TBL1.Entity_ID 

And the part that I am not getting is the outer join (+) on 'Id'. The connection is on the table, ok, but on the line? I have no idea what he is doing.

Does anyone have an idea? Thanks.

Answer 1

TBL4.PROPERTY_NAME(+)='Id' means that when the line was connected by internal volume, then the value should be "Id", but when the line was external, the connection is evaluated as true

however, you must rewrite the statement in the standard as:

 SELECT * FROM TBL1 JOIN TBL2 ON TBL2.Type_ID = TBL1.Type_ID JOIN TBL3 ON TBL3.Project_ID = TBL1.Project_ID LEFT JOIN TBL4 ON TBL4.Entity_ID=TBL1.Entity_ID AND TBL4.PROPERTY_NAME='Id' 

Answer 2

This is the equivalent of the following query using the ANSI join syntax:

 SELECT * FROM TBL1 t1 INNER JOIN TBL2 t2 ON (t1.Type_ID = t2.Type_ID) INNER JOIN TBL3 t3 ON (t3.Project_ID = t1.Project_ID) LEFT JOIN TBL4 t4 ON (t4.Entity_ID = t1.Entity_ID AND t4.PROPERTY_NAME = 'Id') 

You do not join the string simply by specifying a join condition based on one.