Oracle’s outer join syntax (+)

Sometimes I think I might be the only person who’s more comfortable with Oracle’s original outer join syntax than with the ANSI standard.

The way I remember the ANSI standard is by thinking that we call the outer join a LEFT or RIGHT outer join depending on which table we want “extra” from in the result set… the one to the LEFT of the outer join statement, or the one to the RIGHT of it.

For example, in this statement (taken straight from the Oracle SQL reference):

select d.dept_id, e.dept_id 
from departments d LEFT OUTER JOIN employees e 
on d.dept_id=e.dept_id ;

The way I make this intuituive is to think that “departments d” is to the LEFT of the “LEFT OUTER JOIN” keywords, so that’s the one we want to include the “extra” rows from… ie, even rows where no e.dept_id matches the d.dept_id.  But this syntax doesn’t fully line up with my intuition because of the clause “d.dept_id=e.dept_id”: a left outer join returns rows from d where the d.dept_id isn’t matched by any e.dept_id, so the equality isn’t satisified by all rows in the result set.

For that reason I’ve always preferred the old Oracle syntax, even though Oracle itself recommends you use the ANSI standard. It’s more intuitive for me because you simply change the where clause:

select d.dept_id, e.dept_id 
from departments d, employees e 
where d.dept_id=e.dept_id(+) ;

The (+) modifies the where clause, saying that we want all the rows where d.dept_id equals e.dept_id … PLUS the extra outer join rows from d. I suppose I read it as something like “d.dept_id=e.dept_id AND d.dept_id=(+)” where (+) is a shorthand for the rows from d whose dept_id isn’t matched in e. It makes intuitive sense to me.

2 comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.