在 Oracle 中使用 IN 运算符进行外连接的解决方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10758526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Workaround for outer join with an IN operator in Oracle
提问by Chris Cunningham
I am using Oracle SQL, so outer joins have the nice (+) syntax. I should warn you that I am not allowed to redesign the database; I work for a large organization.
我使用的是 Oracle SQL，因此外连接具有很好的 (+) 语法。我应该警告你，我不允许重新设计数据库；我在一家大型组织工作。
Here are some example tables:
以下是一些示例表：
People
PersonID Name
1 Elmo
2 Oscar
3 Chris
Attribute
PersonID Attribute
1 Happy
1 Muppet
1 Popular
2 Grouchy
2 Muppet
2 Popular
3 Programmer
I want a list of people and I want to know whether we have knowledge of them being happy or grouchy. The following is the output I want:
我想要一个人的名单，我想知道我们是否知道他们是快乐还是不高兴。以下是我想要的输出：
Name Mood
Elmo Happy
Oscar Grouchy
Chris
So here is the query I thought I would use:
所以这是我认为我会使用的查询：
SELECT p.Name, a.Attribute
FROM People p, Attributes a
WHERE p.PersonID = a.PersonID (+)
AND ( a.Attribute (+) = 'Happy'
OR a.Attribute (+) = 'Grouchy' )
(Perhaps I would have to put "OR a.Attribute IS NULL" or something.)
（也许我必须输入“OR a.Attribute IS NULL”或其他内容。）
But in fact I'm not allowed to use OR inside an outer join at all! What should I actually do instead?
但实际上我根本不允许在外连接中使用 OR ！我应该怎么做呢？
回答by Lamak
First of all, why can't you use proper OUTER JOINs?, you can use them in Oracle without having to do the implicit joins with the (+)syntax. As for your problem, you can use IN:
首先，为什么不能使用正确的OUTER JOINs？，您可以在 Oracle 中使用它们，而无需使用(+)语法进行隐式连接。至于你的问题，你可以使用IN：
SELECT p.Name, a.Attribute
FROM People p
LEFT OUTER JOIN Attributes a
ON p.PersonID = a.PersonID AND a.Attribute IN ('Happy','Grouchy')
回答by G. Uest
If you really know the Oracel SQL syntax for a "proper" Oracle database, you could also do this...
如果您真的了解“正确”Oracle 数据库的Oracel SQL 语法，您也可以这样做...
SELECT p.Name,
a.Attribute
FROM people p,
(SELECT PersonID,
Attribute
FROM attributes
WHERE Attribute = 'Happy'
OR Attribute = 'Grouchy') a
WHERE p.personid = a.personid(+)
The point being that ANSI vs Oracle syntax is a ridiculous comment. Oracle supports both, you whichever is easier/better/manageable for you.
重点是 ANSI 与 Oracle 语法是一个荒谬的评论。Oracle 支持两者，您可以选择对您来说更容易/更好/更易于管理的方式。
回答by Chris Cunningham
Sorry to answer my own question. To avoid the error ORA-01719, I changed everything to "proper" joins at the advice of @Lamak, and then went with this solution:
很抱歉回答我自己的问题。为了避免错误 ORA-01719，我在@Lamak 的建议下将所有内容更改为“正确”连接，然后采用以下解决方案：
SELECT p.Name, a.Attribute
FROM People p
LEFT OUTER JOIN (SELECT PersonID, Attribute
FROM Attributes
WHERE Attribute = 'Happy' OR Attribute = 'Grouchy') a
ON (p.PersonID = a.PersonID)
