Workaround for outer join with an IN operator in Oracle – This article will take you through the common SQL errors that you might encounter while working with sql, oracle, outer-join. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL 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?
Solution :
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')
If you really know the Oracel SQL syntax for a “proper” Oracle database, you could also do this…
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.
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:
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)
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.