Geek Answers Handbook

SQL (+) = definition and function

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

select * from table where A (+)= B

I ran against:

 select * from table where A = B

and I saw the difference, but I don’t know how to formulate an explanation regarding the fact that (+) = functionality. It seems to me that (+) = tells him to satisfy the condition A = B if it is available, and ignore / enter as "empty" if the components are not available.

In addition, this statement runs inside the view presentation operator.

Thanks in advance.

+6
source share
2 answers

(+) = used to display OUTER JOINS . Now it is not used and canceled (since it is not very readable). (+) denotes an “optional” table in JOIN. I also think that the + notation is only for backward compatibility because Oracle debuted before the ANSI standard for joins was added.

Also note that Oracle recommends using the FROM FROM OUTER JOIN syntax rather than the Oracle join operator.

External join requests using the Oracle (+) join operator are subject to the following rules and restrictions that do not apply to FROM FROM OUTER JOIN:

  • You cannot specify a (+) operator in a query block that also contains the join syntax of a FROM clause.
  • The (+) operator can only be displayed in the WHERE clause or in the context of left correlation (when specifying the TABLE clause) in FROM and can only be applied to a table or view column.
  • If A and B are connected by several join conditions, then you must use the (+) operator in all of these conditions. If you are not, then the Oracle database will return only the rows resulting from a simple join, but without warning or error, to inform you that you have no external join results.
  • The (+) operator does not create an outer join if you specify one table in the outer query and another table in the inner query.
  • You cannot use the (+) operator to externally bind a table to yourself, although self-joins are valid. For example, the following statement
    invalid:
 -- The following statement is not valid: SELECT employee_id, manager_id FROM employees WHERE employees.manager_id(+) = employees.employee_id;

However, the following self-connection is performed:

  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 only be applied to a column, not to an arbitrary expression. However, an arbitrary expression may contain one or more columns marked with the (+) operator.

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

  • The WHERE clause cannot use the IN comparison condition to compare a column labeled with the (+) operator with an expression.

+4
source

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

(go to external connections)

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 these rows from one table for which there are no rows from another that satisfy the join condition.

+3
source

More articles:


All Articles