Oracle: What does `(+)` do in a WHERE clause?

Solution 1:

Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by people who learned it first, since they like that it makes their code shorter.

Best not to use it though, for readability's sake.

Solution 2:

As others have stated, the (+) syntax is obsolete, proprietary syntax that Oracle used for years to accomplish the same results as an OUTER JOIN. I assume they adopted their proprietary syntax before SQL-92 decided on the standard syntax.

The equivalent query to the one you showed, using standard SQL OUTER JOIN syntax (which is now supported by all major RDBMS implementations) would be the following:

SELECT
    Table1.Category1,
    Table1.Category2,
    COUNT(*) AS Total,
    COUNT(Table2.Stat) AS Stat
FROM Table1
  LEFT OUTER JOIN Table2 ON (Table1.PrimaryKey = Table2.ForeignKey)
GROUP BY Table1.Category1, Table1.Category2;

Which means:

• All rows from Table1 are included in the query result.
• Where there are matching rows in Table2, include those rows (repeating content from Table1 if there are multiple matching rows in Table2).
• Where there are no matching rows in Table2, use NULL for all of Table2's columns in the query result.

Solution 3:

It's a non ANSI left outer join notation. Starting with Oracle9i, the confusing outer join syntax using the ‘(+)’ notation has been superseded by ISO 99 outer join syntax.

Solution 4:

A noteworthy consideration is that the classic Oracle notation is not intuitive and is best avoided from a code clarity and maintainability perspective.

To illustrate this point, I have included this example.

To achieve a LEFT outer join between tables A and B one would expect the table on the left which is A should have the (+) operator next to it. This would make sense as we want to denote we would include all rows of A regardless of the success in join criteria with B. However this is not the case and the join is achieved as follows

select b.age, a.name
from Employees a, EmployeeUNI b
where a.id = b.id(+)

I prefer the ANSI SQL version which is explicit:

select b.age, a.name
From Employees a 
    LEFT outer join EmployeeUNI b
on a.id = b.id

Both methods result in the same output however the ANSI approach does not come with the risk of the novice programmer mistakenly putting the (+) in the wrong place.