Question

LEFT JOIN vs. LEFT OUTER JOIN

Oracle, Oracle development, JOIN

I've been googling for 2 hours trying to find if there is a difference between LEFT JOIN and LEFT OUTER JOIN, and of course I didn't find anything. Is there a difference? Is the word 'outer' optional, since a left join is an outer join by default? If this is the case, I don't understand the usage of 'inner' and 'outer', since it would be clear that 'INNER' goes only with 'JOIN' or 'FULL JOIN'(same if inner, right?) as resulting in all that matches in two tables, and 'OUTER' goes with 'LEFT', 'RIGHT' and 'FULL.'

Are those keywords used only to emphasize the nature of the join??

That's a good question, and I'm somewhat surprised you lasted a whole two hours.

The keyword OUTER is optional, but in my opinion it should be mandatory. Until such time as it is (and of course thereafter, too), my advice is always to write it, to remind yourself that it's an outer join.

The keyword LEFT, RIGHT, or FULL is mandatory. Left, right, and full outer joins are the only types of outer join. And of course the keyword JOIN is mandatory.

You mentioned INNER JOIN. This is not the same as FULL [OUTER] JOIN. INNER means all result rows are rows that were produced by match some condition between the two tables. An outer join has result rows where sometimes there isn't a match, yet rows from one table, or the other, or both, are returned without a match.

There is one other type of join besides inner and outer, and that's the CROSS JOIN, but that's a different question for another day.

;o)