Sanity Check: (+) Operator in WHERE Clause for Oracle DB?

Question

Possible Duplicate:
Oracle “(+)” Operator

So I was given a script with the following:

SELECT BLAH
  FROM A_TABLE T1, A_TABLE T2, A_TABLE T3 
 WHERE T2.FIELD_1(+) = T1.FIELD_1
   AND T3.FIELD_1(+) = T2.FIELD_1
   ... etc a few more AND clauses that do that same thing

I need to convert this script to MSSQL Server but I have no clue what this operator is doing. Is it possible that this is some kind of typo?

I *think* that's Oracle-speak for a left join, the `(+)` indicating you get all the T2s even if they don't match the T1s. I might have it backwards. — David Thornley, Mar 14 '11 at 21:34
possible duplicate of [Oracle "(+)" Operator](http://stackoverflow.com/questions/4020786/oracle-operator). Also: http://stackoverflow.com/questions/2425960/oracle-old-joins-a-tool-script-for-conversion — OMG Ponies, Mar 15 '11 at 00:44

score 7 · Accepted Answer · answered Mar 14 '11 at 21:34

7

That's the old Oracle syntax for outer joins. You should use the ANSI join syntax instead:

SELECT BLAH
  FROM A_TABLE T1
  LEFT JOIN A_TABLE T2 ON T2.FIELD_1 = T1.FIELD_1
  LEFT JOIN A_TABLE T3 ON T3.FIELD_1 = T2.FIELD_1
 WHERE ... etc

answered Mar 14 '11 at 21:34

Mark Byers

811,555
193
1,581
1,452

score 3 · Answer 2 · edited May 23 '17 at 12:06

It is the Oracle notation for LEFT JOIN.

It means

SELECT BLAH
  FROM A_TABLE T1
  LEFT JOIN A_TABLE T2 ON T2.FIELD_1 = T1.FIELD_1
  LEFT JOIN A_TABLE T3 ON T3.FIELD_1 = T2.FIELD_1
   ... etc a few more AND clauses that do that same thing

The (+) is put against the optional side. Read also:

Sanity Check: (+) Operator in WHERE Clause for Oracle DB?

2 Answers2