Workaround for outer join with an IN operator in Oracle

oracle, outer-join, sql

Solution

First of all, why can't you use proper `OUTER JOIN`s?, 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')

Problem

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. 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.) But in fact I'm not allowed to use OR inside an outer join at all! What should I actually do instead?

Original source