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

Question

Found the following in an Oracle-based application that we're migrating (generalized):

SELECT
    Table1.Category1,
    Table1.Category2,
    count(*) as Total,
    count(Tab2.Stat) AS Stat
FROM Table1, Table2
WHERE (Table1.PrimaryKey = Table2.ForeignKey(+))
GROUP BY Table1.Category1, Table1.Category2

What does (+) do in a WHERE clause? I've never seen it used like that before.

Answer 1

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.

Answer 2

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.

Answer 3

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.