Oracle "(+)" Operator

Question

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.

The DBMS is Oracle.

I did not understand a statement which read like this:

select ...
from a,b
where a.id=b.id(+)

I am confused about the (+) operator, and could not get it at any forums... (searching for + within quotes didn't work either).

Anyway, I used 'Explain Plan' of SQLDeveloper and I got an output saying that HASH JOIN, RIGHT OUTER, etc.

Would there be any difference if I remove the (+) operator at the end of the query? Does the database have to satisfy some condition (like having some indexes, etc.) before (+) can be used?

It's not an operator. It's just a piece of syntax that affects what JOIN does. — philipxy, Mar 05 '19 at 10:50
Oracle refers to it as an operator https://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52336 — malckier, Sep 01 '21 at 14:59
@malckier Calling it an operator doesn't make it an operator. If the documentation were better maybe people would understand what it's trying to say. — philipxy, Jan 07 '22 at 21:16

score 224 · Accepted Answer · edited Mar 31 '16 at 18:40

224

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

It should also be noted that even though the (+) works, Oracle recommends not using it:

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, which do not apply to the FROM clause OUTER JOIN syntax:

edited Mar 31 '16 at 18:40

Mr. Llama

20,202
2
62
115

answered Oct 26 '10 at 04:54

OMG Ponies

325,700
82
523
502

94

Exactly right. In order to keep the (+) straight in my head (left side vs. right side), I like to think of the (+) as "adding NULL values if no match found". For example, "a.id=b.id(+)" means allow b.id to be NULL if there is no match with a.id. – beach Oct 26 '10 at 05:00
You may want to have a link at the official Oracle documentation: http://docs.oracle.com/html/A95915_01/sqopr.htm – Vargan Jun 03 '15 at 16:55
1

I had to double take on the order that you specified the join criteria. I usually see it as SELECT * FROM a LEFT JOIN b ON a.id = b.id ... not b.id = a.id.... Not that that matters. – John Kurtz Jul 19 '21 at 15:18
Interesting, @JohnKurtz... I would do it always opposite (ie. b.id = a.id) to see from the perspective of the freshly joined b how it hooks in the existing tables of the query. – JRA_TLL Jul 13 '22 at 07:40

Md. Salman Fahad Famous · Answer 2 · 2019-04-07T13:08:55.103

In Oracle, (+) denotes the "optional" table in the JOIN. So in your query,

SELECT a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id(+)

it's a LEFT OUTER JOIN of table 'b' to table 'a'. It will return all data of table 'a' without losing its data when the other side (optional table 'b') has no data.

The modern standard syntax for the same query would be

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b ON a.id=b.id

or with a shorthand for a.id=b.id (not supported by all databases):

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a
LEFT JOIN b USING(id)

If you remove (+) then it will be normal inner join query

Older syntax, in both Oracle and other databases:

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id=b.id

More modern syntax:

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a
INNER JOIN b ON a.id=b.id

Or simply:

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a
JOIN b ON a.id=b.id

It will only return all data where both 'a' & 'b' tables 'id' value is same, means common part.

If you want to make your query a Right Join

This is just the same as a LEFT JOIN, but switches which table is optional.

Old Oracle syntax:

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a,b
WHERE a.id(+)=b.id

Modern standard syntax:

SELECT  a.id, b.id, a.col_2, b.col_2, ...
FROM a
RIGHT JOIN b ON a.id=b.id

Ref & help:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6585774577187

Left Outer Join using + sign in Oracle 11g

https://www.w3schools.com/sql/sql_join_left.asp

The circle labels "table1" & "table2" are nonsense. The simplest meaning for circle elements is output rows. Then the left crescent contains the null-extended rows of table1 & the right crescent contains the null-extended rows of table2. Other meanings for the circles are obscure. What exactly do you think the diagrams mean? Why did you put them in other than blindly copying other people's (bad) presentations? — philipxy, Mar 05 '19 at 11:09
You have now labeled the circles a & b. The circles are not rows of a & b. The left & right circles could reasonably be labeled rows of a left join b & a right join b. Moreover, you don't describe what those encircled rows are in terms of the input. Work out for yourself what things are in the circles. If you are going to stick labels a & b on the circles, explain clearly in words what each label has to do with its circle. (There is no straightforward reason to label them a & b.) It's good that you labelled the green zones correctly. — philipxy, Apr 07 '19 at 19:32

score 29 · Answer 3 · edited Oct 22 '13 at 12:09

The (+) operator indicates an outer join. This means that Oracle will still return records from the other side of the join even when there is no match. For example if a and b are emp and dept and you can have employees unassigned to a department then the following statement will return details of all employees whether or not they've been assigned to a department.

select * from emp, dept where emp.dept_id=dept.dept_id(+)

So in short, removing the (+) may make a significance difference but you might not notice for a while depending on your data!

score 6 · Answer 4 · answered Aug 02 '17 at 17:21

6

In practice, the + symbol is placed directly in the conditional statement and on the side of the optional table (the one which is allowed to contain empty or null values within the conditional).

answered Aug 02 '17 at 17:21

sunny

158
1
2
13

The (+) is put immediately to the right of a column name. This answer isn't clear about that. What is "in practice" supposed to mean? (Rhetorical.) (And you probably don't mean "statement".) – philipxy Feb 14 '20 at 02:08

Oracle "(+)" Operator

4 Answers4

If you remove (+) then it will be normal inner join query

If you want to make your query a Right Join

Ref & help:

Linked

Related