7

I'm migrating a client's software database from Oracle to PostgreSQL, and I have some trouble understanding a query, what it does, and consequently how to migrate it.

The query is:

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 I don't get, is the outer join (+) on 'Id'. A join on a table, OK, but on a string? I've no idea of what it does.

Do someone has an idea? Thanks.

Tiller
  • 436
  • 1
  • 4
  • 22

2 Answers2

7

TBL4.PROPERTY_NAME(+)='Id' means when the line was inner joined, then the value has to be 'Id', but when the line was outer joined, the condition is evaluated as true

however you should rewrite the statement to 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'
Pavel Gatnar
  • 3,987
  • 2
  • 19
  • 29
1

This is the equivalent of the following query using 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're not joining to a string, merely specifying a join condition that's based on one.

ninesided
  • 23,085
  • 14
  • 83
  • 107