11

I always tell new people that an easy way to remember the old-style, Oracle outer-join syntax is that

the (+) sign is on opposite side of where you think it should be.

Left join:

select * from foo, bar
where foo.id = bar.id(+)

Right join:

select * from foo, bar
where foo.id(+) = bar.id

I'm sure I learned this in college, but what is the purpose of having the (+) sign on either side? Does it simply indicate "even those rows on the other side that don't match on this side"? but that would seem too complicated. What was the purpose of choosing "(+)" and putting it on the side where no match was made?

Matthew Moisen
  • 14,590
  • 25
  • 104
  • 205

2 Answers2

13

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.

Justin Cave
  • 221,607
  • 22
  • 353
  • 373
  • Would you happen to have any comment on why the "(+)" was chosen? – Matthew Moisen Mar 27 '14 at 08:07
  • 1
    Good Explanation. However, I think there is a "to" too many in "The (+) identifies the table that is being outer joined to", so the sentence is confusing. In `foo.id = bar.id(+)` bar is the table that is being outer joined, and foo is the table that (bar) is being outer joined *to*. – Thorsten Kettner Dec 12 '14 at 11:38
-1

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)
Eugenio F. Martinez Pacheco
  • 386
  • 1
  • 10
  • 1
    None of the 2 statements above are full outer joins. One is a left join, one is a right join, as correctly stated in the question. The question asked *why* Oracle and others chose to put the (+) on the outer joined table and not on the main table, and your answer doesn't even try to address that. See the accepted answer for an example of good answer to this question. – p91paul Jul 14 '21 at 16:48
  • "what is the purpose of having the (+) sign on either side?" what means to you? I interpret having the plus sign in both sides, sorry for the inconvenience, if there is any. – Eugenio F. Martinez Pacheco Aug 16 '21 at 10:46
  • 1
    I see. I am not a native English speaker, but I think either and both are not synonyms. Either means an alternative, e.g. "this thing is either black or white, but it can't be both". So I interpret that question to be "when there is only one (+) for a left/right join, why did Oracle developers decide it should be on that side and not the other?" – p91paul Aug 17 '21 at 07:28
  • Thus complementary answer, not wrong at all. Editing to include your amending. Regards! – Eugenio F. Martinez Pacheco Aug 18 '21 at 14:59