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.Per

Solution 1:

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
LEFTOUTERJOIN Attributes a
ON p.PersonID = a.PersonID 
WHERE a.Attribute IN ('Happy','Grouchy') -- This means that the right side must exist alsoAND p.person_id IN ('Elmo', 'Oscar') 

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

Solution 2:

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.

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.

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

LEFTOUTERJOIN 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:

LEFTOUTERJOIN (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.

Solution 3:

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).

Solution 4:

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

SELECT p.Name, a.Attribute
FROM People p INNERJOIN
     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 JOINand to move the condition on the second table to the ON clause:

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