SQL (+)= definition and function

Question

I would like to know what this statement in SQL actually does:

select * 
from table 
where A (+)= B

I ran it against:

select * 
from table 
where A = B

and saw the difference but do not know how to formulate an explanation as to what (+)='s functionality is. It seems to me that (+)= is telling it to make it satisfy the condition A = B where available and ignore/enter as "empty" if components are not available.

Also, this statement is ran within a create view statement.

Thanks in advance.

It is left outer join statement which is deprecated. Have a look at this http://stackoverflow.com/questions/6559261/left-outer-join-using-sign-in-oracle-11g — Jacob, Feb 24 '14 at 07:38
possible duplicate of [What does (+) do in Oracle SQL?](http://stackoverflow.com/questions/1376442/what-does-do-in-oracle-sql) — Jon Heller, Feb 25 '14 at 04:23

Rahul Tripathi · Accepted Answer · 2014-02-24T10:47:12.680

(+)= is a used to show OUTER JOINS. It is not used now and is deprectaed now(as it is not very much readable). (+) denotes the "optional" table in the JOIN. Also I think that + notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins was put in place.

Also note that 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:

You cannot specify the (+) operator in a query block that also contains FROM clause join syntax.

The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.

If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.

You cannot use the (+) operator to outer-join a table to itself, although self joins are valid. For example, the following statement
is not valid:

-- The following statement is not valid:
     SELECT employee_id, manager_id 
    FROM employees
    WHERE employees.manager_id(+) = employees.employee_id;

However, the following self join is valid:

   SELECT e1.employee_id, e1.manager_id, e2.employee_id
    FROM employees e1, employees e2
    WHERE e1.manager_id(+) = e2.employee_id
    ORDER BY e1.employee_id, e1.manager_id, e2.employee_id;

The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.

A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.

A WHERE condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.

So in theory it could be group like this? A(+) = B .. and so this is logically the same as B = A(+)? — Henry Fok, Feb 24 '14 at 07:52
I don't like the (+) syntax but it's not accurate to say it's deprecated. The syntax is still used in almost all of Oracle-generated SQL statements, it is required for some features such as bitmap join indexes and fast refresh materialized views, and the syntax was extended in 12c to remove some of the limitations. — Jon Heller, Feb 25 '14 at 04:26
@jonearles:- Yes I agree that it is not deprectaed but I just wrote it DEPRECTAED as Oracle also does not recommend to use it. Should I remove the deprecated word if it is causing some trouble? — Rahul Tripathi, Feb 25 '14 at 04:28
It's a tough call, it depends on the audience. It's a useful thing to say to someone new to Oracle. But you wouldn't want to say that to an experienced Oracle developer since it's technically wrong. — Jon Heller, Feb 25 '14 at 04:41
@jonearles:- Yes that was the reason why I mentioned the reason in brackets when I said deprecated! ;) — Rahul Tripathi, Feb 25 '14 at 04:42

score 3 · Answer 2 · answered Feb 24 '14 at 07:38

See oracle doc here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

(go down to outer joins)

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

SQL (+)= definition and function

2 Answers2