Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join? Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join? oracle oracle

Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join?


The (+) identifies the table that is being outer joined to. The way I was taught, the (+) indicated the table that would have missing rows for which new NULL rows had to be added.

If you look at the alternate left outer join syntaxes that various databases supported before LEFT OUTER JOIN became part of the ANSI standard, the proprietary operator was generally applied to the table that was "missing" rows. DB2 also supports the (+) operator for outer joins in the same way that Oracle does.


Easy one... it is a FULL OUTER JOIN.

As stated by p91paul, either is not strictly equal to both, so my answer is not accurate enough, but complementary. Regards!

That's all, folks

So this way you have the complete set of joins between a set A and another set B:

  • inner (only common to A and B),
  • left (all A, matched B or null otherwise)
  • right (all B, matched A or null otherwise)
  • full (all A, all B, matched or not)