Oracle在字符串上的OUTER JOIN(+)-迁移PostgreSQL [英] Oracle's OUTER JOIN (+) on string - Migration PostgreSQL

问题描述

我正在将客户端的软件数据库从Oracle迁移到PostgreSQL，在理解查询，查询的功能以及如何迁移查询方面遇到一些麻烦.

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.

查询是:

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

我不了解的部分是'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.

推荐答案

TBL4.PROPERTY_NAME(+)='Id'表示该行是内部联接的，则该值必须为'Id'，而当该行是外连接，条件评估为真

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' 

这篇关于Oracle在字符串上的OUTER JOIN(+)-迁移PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！