(+) symbol in SQL Query

Question

Just i have came across a SQL query in one of the stored procedure like below:

SELECT 
    * 
FROM 
    account a, 
    performance p,
    customer c, 
    override o
WHERE 
    a.account_id = p.account_id (+)
    AND a.account_id = c.account_id (+)
    AND o.override_type(+) = 'O'

Can you please explain what is the (+) symbol's play here? and the difference of using Left side and right side.

Thanks in advance.

possible duplicate of [What does (+) do in Oracle SQL?](http://stackoverflow.com/questions/1376442/what-does-do-in-oracle-sql) — Ben, Mar 05 '13 at 08:31
Though this is a better explanation: http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation — Ben, Mar 05 '13 at 08:32

Mahmoud Gamal · Accepted Answer · 2013-03-05T07:10:06.127

5

It is the old syntax for OUTER JOIN in Oracle (I don't know whether there are other RDBMS that uses the same old syntax or not).

Better off: Use the explicit ANSI-92 OUTER JOIN syntax using LEFT OUTER JOIN or RIGHT OUTER JOIN instead of the + symbol.

edited Mar 05 '13 at 07:10

answered Mar 05 '13 at 07:03

Mahmoud Gamal

78,257
17
139
164

This Stored procedure is somewhat 7 or 8 years old. Thanks for clearing my doubt. – SuRa Mar 05 '13 at 07:10
@Sundar - You're welcome any time:) What RDBMS is this? Is it Oracle? – Mahmoud Gamal Mar 05 '13 at 07:11

score 1 · Answer 2 · answered Mar 05 '13 at 07:36

1

(+) is an legacy outer join syntax in oracle (8 and before). It is very restrictive and handles many cases just wrong. Don't use it anymore. Oracle supports ansi joins (eg. left outer join) since version 9.

answered Mar 05 '13 at 07:36

Stefan Steinegger

63,782
15
129
193

You're going to have to come up with proof for "handles many cases just wrong". It has various restrictions but not that much more than the ANSI syntax and sometimes still _has_ to be used because Oracle's implementation of the ANSI syntax sometimes results in incorrect query plans where the + syntax doesn't (though I prefer not to use it). – Ben Mar 05 '13 at 08:35
You can't for instance outer join from a table that is already outer joined (e.g. order outer join customer outer join address). You probably can't even join anymore after outer joining. There are issues when you have additional condition on an outer joined table. It's not really documented. The problem is that it doesn't complain when you write an invalid query. It just returns plain wrong data. Years ago I remember that I got values returned by the queries which appear nowhere on that column in the whole database. As much as I like Oracle databases, this outer joins are failed. – Stefan Steinegger Mar 05 '13 at 13:44

(+) symbol in SQL Query

2 Answers2