在Oracle中使用IN运算符进行外部联接的解决方法 [英] Workaround for outer join with an IN operator in Oracle

问题描述

我正在使用Oracle SQL，因此外部联接具有不错的(+)语法.我应该警告您，不允许我重新设计数据库；我为一个大型组织工作.

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.

以下是一些示例表:

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

这是我想使用的查询:

SELECT p.Name, a.Attribute
FROM People p, Attributes a
WHERE p.PersonID = a.PersonID (+)
AND ( a.Attribute (+) = 'Happy'
   OR a.Attribute (+) = 'Grouchy' )

(也许我将必须输入"OR a.Attribute IS NULL"或其他内容.)

(Perhaps I would have to put "OR a.Attribute IS NULL" or something.)

但是实际上我根本不允许在外部联接中使用OR！我实际上应该怎么做?

But in fact I'm not allowed to use OR inside an outer join at all! What should I actually do instead?

推荐答案

首先，为什么不能使用正确的OUTER JOIN?您可以在Oracle中使用它们而不必使用(+)语法.对于您的问题，您可以使用IN:

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:

SELECT p.Name, a.Attribute
FROM People p
LEFT OUTER JOIN Attributes a
ON p.PersonID = a.PersonID AND a.Attribute IN ('Happy','Grouchy')

这篇关于在Oracle中使用IN运算符进行外部联接的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！