Difference between Oracle's plus (+) notation and ansi JOIN notation?

What's the difference between using oracle's plus notation (+) over the ansi standard join notation?

Is there a difference in performance?

Is the plus notation deprecated?

AFAIK, the (+) notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins was put in place. It's specific to Oracle and you should avoid using it in new code when there's an equivalent standards-compliant version available.

It seems there are differences between the two, and the (+) notation has restrictions that the ANSI join syntax does not have. Oracle themselves recommend that you not use the (+) notation. Full description here in the Oracle® Database SQL Language Reference 11g Release 1 (11.1):

> 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: > 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; >
• 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. > If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle returns only the results of a simple join. > In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. Refer to SELECT for the syntax for an outer join.

The most comprehensive answer obviously is the one by nagul.

An addition for those who are looking for quick translation/mapping to the ANSI syntax:

--
-- INNER JOIN
--
SELECT *
FROM EMP e
INNER JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

 -- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
	 DEPT d
WHERE d.DEPTNO = e.DEPTNO;

--
-- LEFT OUTER JOIN
--
SELECT *
FROM EMP e
LEFT JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

 -- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
	 DEPT d
WHERE d.DEPTNO (+) = e.DEPTNO;

--
-- RIGHT OUTER JOIN
--
SELECT *
FROM EMP e
RIGHT JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

-- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
	 DEPT d
WHERE d.DEPTNO = e.DEPTNO(+);

--
-- CROSS JOIN
--
SELECT *
FROM EMP e
CROSS JOIN DEPT d;

 -- Synonym in deprecated oracle (+) syntax
SELECT *
FROM EMP e,
	 DEPT d;

--
-- FULL JOIN
--
SELECT *
FROM EMP e
FULL JOIN DEPT d ON d.DEPTNO = e.DEPTNO;

-- Synonym in deprecated oracle (+) syntax !NOT WORKING!
SELECT *
FROM EMP e,
	 DEPT d
WHERE d.DEPTNO (+) = e.DEPTNO(+);

The notation is still supported as of Oracle 10 (and I believe 11). It's use is considered "old fashioned", and also is not as database portable as the ANSI JOIN syntax. It's also considered much less readable, although if you come from the + background getting used to ANSI JOIN can take a little time. The important thing to know before hurling brickbats at Oracle is that they developed their + syntax before the ANSI committee had completed the definitions for the joins.

There is no performance difference; they are expressing the same thing.

Edit: By "not as portable" I should have said "only supported in Oracle SQL"

I agree with Tony Miller's answer and would like to add that there are also a few things that you can NOT do with the (+) synthax:

• You can not FULL OUTER JOIN two tables, you have to do it manually with a UNION ALL of two joins,
• You can not OUTER JOIN a table to two or more tables, you have to manually create a subquery (ie: b.id = a.id (+) AND c.id = a.id (+) is not an acceptable clause)

One of the good reasons to use ANSI syntax over the old Oracle join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older Oracle join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them.

Difference between Oracle outer join syntax and the ANSI/ISO Syntax.

LEFT OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+);

SELECT e.last_name,
  d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

RIGHT OUTER JOIN -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.last_name,
  d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

FULL OUTER JOIN -

Before the native support of hash full outerjoin in 11gR1, Oracle would internally convert the FULL OUTER JOIN the following way -

SELECT e.last_name,
  d.department_name
FROM employees e,
  departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT NULL,
  d.department_name
FROM departments d
WHERE NOT EXISTS
  (SELECT 1 FROM employees e WHERE e.department_id = d.department_id
  );
  
SELECT e.last_name,
  d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

Have a look at this.

Oracle (+) notation is only used in Oracle, which is vendor specific. And,ANSI standared Join notation can be used in any RDBMS (like Sql Server,MySql etc.). Otherwise,there is no difference between Oracle (+) notation and ANSI standared Join notation.

If you are using the ANSI standared Join notation in your Sql Query, you can use the same query in any RDBMS. And, if you are porting your database from Oracle to any Other RDBMS in that condition you have to use ANSI Syntax.

1. Use explicit JOINs rather than implicit (regardless whether they are outer joins or not) is that it's much easier to accidently create a cartesian product with the implicit joins. With explicit JOINs you cannot "by accident" create one. The more tables are involved the higher the risk is that you miss one join condition.
2. Basically (+) is severely limited compared to ANSI joins. Furthermore it is only available in Oracle whereas the ANSI join syntax is supported by all major DBMS
3. SQL will not start to perform better after migration to ANSI syntax - it's just different syntax.
4. Oracle strongly recommends that you use the more flexible FROM clause join syntax shown in the former example. In the past there were some bugs with ANSI syntax but if you go with latest 11.2 or 12.1 that should be fixed already.
5. Using the JOIN operators ensure your SQL code is ANSI compliant, and thus would allow a front-end application to be more easily ported for other database platforms.
6. Join conditions have a very low selectivity on each table and a high selectivity on the tuples in the theoretical cross product. Conditions in the where statement usually have a much higher selectivity.
7. Oracle internally converts ANSI syntax to the (+) syntax, you can see this happening in the execution plan's Predicate Information section.

I use (+) notation, because almost all of the Oracle Apps r12 related queries are based on that. I've not seen a single SQL query with a standard "join" expression in Oracle APPS queries(even the ones provided by Oracle itself). If you don't believe me, simply google any Oracle apps related information. For example: Fixed assets related queries