Oracle SQL: Meaning of (+)= in WHERE clause

Question

I have a question about the using of (+)= in the where clause in Oracle database;

a. id= b.id(+)

Does that mean a left join that a left join on b where a.id=b.id, right?

a.Job_Type(+) = 'Manager'

I don't understand why he uses (+)= here, but not a.Job_Type = 'Manager', are they the same?

The ambiguity of `(+)` is why I recommend **not** using it. 1) INNER/LEFT/RIGHT are much more explicit, 2) it keeps your join criteria separate from your filtering criteria, and 3) it's more flexible (i.e. try using `(+)` for `UPPER(x) = UPPER(y)` or `x LIKE y`) — Mr. Llama, Mar 31 '16 at 17:25
See [this answer](http://stackoverflow.com/a/28498944/266304) for an explanation with examples. The link in the first comment might be the best duplicate target though. — Alex Poole, Mar 31 '16 at 17:42
@Mr.Llama: not only you recommend not using it. Even Oracle does. — , Mar 31 '16 at 18:31
@a_horse_with_no_name - I had to do some digging but you're [absolutely right](https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52355). "Oracle recommends that you use the `FROM` clause `OUTER JOIN` syntax rather than the Oracle join operator." — Mr. Llama, Mar 31 '16 at 18:39
A complete WHERE clause would make it easier for us to explain this ancient syntax. `a.Job_Type(+) = 'Manager'` outer joins table `a` to some data and `a. id= b.id(+)` then outer joins table `b` to table `a`. But you would usually not see `a.Job_Type(+) = 'Manager'` as the only outer join criteria on `a`. And `a. id= b.id(+)` would usually indicate a self-join, for two different tables wouldn't normally share the ID. — Thorsten Kettner, Mar 31 '16 at 19:36
The question 1 I understand, my confusion is question 2,it is a simply where clause, just say give me whose Job Type is Mananger; but what hte difference between a.Job_Type(+) = 'Manager' and .Job_Type= 'Manager' — Worst SQL Noob, Mar 31 '16 at 20:35

score 2 · Answer 1 · answered Mar 31 '16 at 17:13

it works like LEFT JOIN and RIGHT JOIN depending on the table that column belong to you can read about different types of JOINS here

in your case (+) will return the all records from table which has alias a and only those records from table which has alias b that intersect with table alias a.

score 0 · Accepted Answer · edited May 23 '17 at 10:32

The (+) identifies the table that is being outer joined to. The way I was taught, the (+) indicated the table that would have missing rows for which new NULL rows had to be added.

If you look at the alternate left outer join syntaxes that various databases supported before LEFT OUTER JOIN became part of the ANSI standard, the proprietary operator was generally applied to the table that was "missing" rows. DB2 also supports the (+) operator for outer joins in the same way that Oracle does.

Answer: Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join?

Oracle SQL: Meaning of (+)= in WHERE clause

2 Answers2