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

Question

In the Oracle application we port (generically) found the following:

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 (+) in a WHERE clause? I have never seen this used before.

Answer 1

Depending on which side "=" is turned on (+), it denotes a LEFT OUTER or RIGHT OUTER join (in this case, it is a left outer join). This is the old Oracle syntax, which is sometimes preferable to people who learned it first, since they like it, which makes their code shorter.

It is better not to use it, although for readability.

Answer 2

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

The equivalent query to the one you showed using the standard SQL OUTER JOIN (which is now supported by all major RDBMS implementations) will be as follows:

 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; 

It means:

• All rows from Table1 are included in the query result.
• If there are matching rows in Table2 , specify these rows (repeating the contents of Table1 , if there are several matching rows in Table2 ).
• If Table2 does not have matching rows, use NULL for all Table2 columns in the query result.

Answer 3

This is an external notation without ANSI. Starting with Oracle9i, the embarrassing outer join syntax using the '(+) notation has been replaced by the ISO 99 outer join syntax.