CODEWITHSUNDEEP
sqloracle-database
oyed
oyed

Reputation: 421

Why can't you use OR or IN with a OUTER JOIN operation?

If I use the following query in an Oracle SQL database (inspired by this question here):

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

I will get the error:

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

While this should fix it:

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

Could anyone explain what is the reason the first version invokes an error? What's the actual difference?

Upvotes: 2

Views: 4196

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Your query is an inner join, so you might as well write:

SELECT p.Name, a.Attribute
FROM People p INNER JOIN
     Attributes a
     ON p.PersonID = a.PersonID 
WHERE a.Attribute IN ('Happy','Grouchy') AND
      p.person_id IN ('Elmo', 'Oscar');

Any use of LEFT JOIN is undone by the WHERE clause, because of the NULL value in a.Attribute.

If you want a person even with no matches, then you do want a LEFT JOIN and to move the condition on the second table to the ON clause:

SELECT p.Name, a.Attribute
FROM People p INNER JOIN
     Attributes a
     ON p.PersonID = a.PersonID AND
        a.Attribute IN ('Happy', 'Grouchy')
WHERE p.person_id IN ('Elmo', 'Oscar');

Upvotes: 0

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

Could anyone explain what is the reason the first version invokes an error?

It is just the way Oracle decided to do it: invoking the error on such use of OUTER JOIN: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4247209685061

What's the actual difference?

Here I may be able to give you a fuller explanation (I hope)...

OUTER JOIN, in contrast to INNER JOIN, is designed to allow you to select all rows from Table A (in your example table people) with corresponding data from Table B joined in the same resulting row, if it exists. That means that OUTER JOIN allows for nonexistent Table B data.

enter image description here

When you put columns from your table B into WHERE clause, you are making their existence mandatory (they must exist if you are comparing them) thus effectively making your join an INNER JOIN.

enter image description here

On the other hand, when you wrote the following code:

LEFT OUTER JOIN Attributes a
ON p.PersonID = a.PersonID AND a.Attribute IN ('Happy','Grouchy') AND p.person_id IN ('Elmo', 'Oscar')

it is equivalent as if you've written this:

LEFT OUTER JOIN (SELECT *
                   FROM Attributes
                  WHERE Attribute IN ('Happy','Grouchy')
                ) a   ON (p.PersonID = a.PersonID)
WHERE p.person_id IN ('Elmo', 'Oscar')

and thus you have resumed using OUTER JOIN.

Upvotes: 2

Martin Preiss
Martin Preiss

Reputation: 396

it's just the way Oracle implemented the (+) operator (many years ago): some things do not work with it. Another example is the full outer join (which can only be formulated with the help of a UNION ALL with the (+) operator).

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

You've defined an inner join there, by the way.

By having a clause against the right hand table, it must meet both criteria.

SELECT p.Name, a.Attribute
FROM People p
LEFT OUTER JOIN Attributes a
ON p.PersonID = a.PersonID 
WHERE a.Attribute IN ('Happy','Grouchy') -- This means that the right side must exist also
AND p.person_id IN ('Elmo', 'Oscar')

Either use INNER JOIN or move this WHERE to the ON clause as an AND

Upvotes: 8

Related Questions