What does (+) mean in SQLPlus?

Question

I just stumbled across a SQL+-Query which uses (+). I've never worked with SQL+ before and I've never seen something like this. I tried to ask Google about it, but I couldn't find anything useful since Google obviously filters the "(+)" and just ignores it...

Example:

[...]
where [...] 
    AND 16791688 = T7mm.child_fielddef_id  (+)  
    AND T7mm.parent_dbid = T7.dbid  (+) 
    AND T1.dbid <> 0 [...]

I would suggest using http://symbolhound.com/ for searching something like that see [the results](http://symbolhound.com/?q=%28%2B%29+oracle) — Habib, Aug 20 '13 at 13:06
@ConradFrix possible, though I searched for it on SO and didn't find that particular question. Thanks for the enlightment anyways :) — Vince, Aug 20 '13 at 13:08
It would be better to rewrite this using LEft joins if the version of Oracle you are using supports them. — HLGEM, Aug 20 '13 at 13:08
Vince - check out the link from Conrad, which will answer your question. Basically the (+) is old syntax for an outer join. Also note that it's an Oracle thing, not a SQLPlus thing. — Ed Gibbs, Aug 20 '13 at 13:08
@Vince You missed it because of what Ed Gibbs pointed out. You were looking for SQL plus instead of Oracle and also because as you pointed out (+) is hard to look for — Conrad Frix, Aug 20 '13 at 13:12

score 4 · Accepted Answer · answered Aug 20 '13 at 13:08

4

it is an Oracle specific shortcut for OUTER JOIN

answered Aug 20 '13 at 13:08

Randy

16,480
1
37
55

hmmmm... although `(+)` is for outer joins, I wouldn't characterize it as *shortcut* in the normal sense of the word, i.e. it cannot be simply used in place of `OUTER JOIN` as its syntax is different. – Jeffrey Kemp Aug 21 '13 at 01:05

score 3 · Answer 2 · answered Aug 20 '13 at 13:12

It makes the join an outer join rather than an inner join:

SELECT
    A.*,
    B.*
FROM
    A,
    B
WHERE
    A.ID = B.ID(+)

is equivalent to:

SELECT
    A.*,
    B.*
FROM
    A
    LEFT JOIN B ON A.ID = B.ID

The (+) notation is the old Oracle syntax for SQL queries. Now it is generally viewed as best practice to use the ANSI standard with the LEFT JOIN keywords instead

What does (+) mean in SQLPlus?

2 Answers2