102

Possible Duplicate:
Oracle: What does (+) do in a WHERE clause?

Consider the simplified SQL query below, in an Oracle database environment (although I'm not sure that it's Oracle-specific):

SELECT 
   t0.foo, t1.bar
FROM
   FIRST_TABLE t0, SECOND_TABLE t1
WHERE
   t0.ID (+) = t1.ID;

What is that (+) notation for in the WHERE clause? I'm sorry if this is an ignorant newbie question, but it's been extremely difficult to search for on Google or StackOverflow... because even when using quote marks, search engines see a '+' sign and seem to want to treat it as some kind of a logical directive.

Community
  • 1
  • 1
Steve Perkins
  • 11,520
  • 19
  • 63
  • 95
  • 1
    And: [Oracle “(+)” Operator](http://stackoverflow.com/questions/4020786/oracle-operator) – OMG Ponies Nov 18 '10 at 16:52
  • Also: [Oracle (Old?) Joins - A tool/script for conversion?](http://stackoverflow.com/questions/2425960/oracle-old-joins-a-tool-script-for-conversion) – OMG Ponies Nov 18 '10 at 16:52
  • 2
    Fair enough. I'll vote to delete... although since it's now closed, apparently I have to wait 2 days before I can do so (some of these Stack Overflow procedures make zero sense to me). – Steve Perkins Nov 18 '10 at 16:57
  • 3
    Ugh. Stack Overflow tells me I have to wait 2 days before voting to delete, for some silly reason that made little sense. I wait, and then it tells me I *can't* delete because there are too many answers or upvotes. However, the number of answers and upvotes had not changed over the past two days... so it could have just said that to begin with! At any rate, we have a duplicate question... live with it, I guess. No wonder this site gives out various "medals" for basic usage. Users deserve them! – Steve Perkins Nov 21 '10 at 14:02
  • 1
    I was looking for the meaning of these `(+)` in SQL queries for Oracle. And no way to search for `(+)` with Google. I headed to Stack Overflow, and the search engine of Stack Overflow gave me zero result for `(+)`. Which is wrong. There are at least two questions here on Stack Overflow about the meaning of `(+)`. It would be nice to correct the search engine. – Nicolas Barbulesco Jul 29 '13 at 14:55
  • @NicolasBarbulesco, as of 2/2022, Google returns this page as #2 result for 'what's "(+)" in sql', and #1 is for Oracle outer join operator (+) allows you to perform outer joins on two or more tables" featured snippet. – Pere Feb 14 '22 at 19:58

1 Answers1

83

This is an Oracle-specific notation for an outer join. It means that it will include all rows from t1, and use NULLS in the t0 columns if there is no corresponding row in t0.

In standard SQL one would write:

SELECT t0.foo, t1.bar
  FROM FIRST_TABLE t0
 RIGHT OUTER JOIN SECOND_TABLE t1;

Oracle recommends not to use those joins anymore if your version supports ANSI joins (LEFT/RIGHT JOIN) :

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions […]

Benoit
  • 76,634
  • 23
  • 210
  • 236
  • 3
    +1 Probably worth mentioning that recent versions of Oracle (10g onwards, I think) support SQL-92 syntax, which allows outer joins to be explicitly specified (instead of using the legacy (+) operator). –  Nov 18 '10 at 16:45
  • 2
    @Mark Bannister: 8i had some, but it was readily accessible in 9i. – OMG Ponies Nov 18 '10 at 16:54
  • 2
    At last, this was a pain in the back to google. – Pieter De Bie Oct 06 '15 at 11:49
  • Sorry for the late query. What does it mean when the plus appears on the right side of condition or on the left side of the condition ? – khalidmehmoodawan Apr 16 '19 at 05:58
  • 1
    Interesting that this has been accepted for so long. According to Oracle's documentation linked to in the answer, "To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B." It seems that Benoit reversed the join that he described in her/his answer. – zielot Aug 13 '20 at 16:52
  • 1
    @zielot I think the "RIGHT OUTER JOIN" here is correct because in the original question the (+) was included on the left side. https://stackoverflow.com/a/4020794/161022 explains it better – lmsurprenant Oct 18 '22 at 18:59