You should avoid using the '(+)' notation and upgrade the queries to use explicit joins.
–
Jonathan LefflerJul 2 '11 at 20:50

@JonathanLeffler 100% agree. The problem is that I work with people who don't want to swith to the standard notation. I write new query with standard notation but I'll be shoot in a minute if a modified an old one.
–
Luc MAug 7 '12 at 14:24

@Amyth Sorry for this way outdated comment, but I came to this question from a search. As of myself, I understand Oracle's recommendations the exact opposite way. From your link: "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 ..."
–
Sylvain LerouxOct 23 '14 at 9:40

Sorry for the outdated response as well :), but what Oracle says, and how its stats work to optimize the queries are two different things, and it might be that Oracle has changed its stance as well with advances to its internal optimizers
–
A mythJan 28 at 15:22

3 Answers
3

In Oracle, (+) denotes the "optional" table in the JOIN. So in your first query, it's a P LEFT OUTER JOIN S. In your second query, it's S RIGHT OUTER JOIN P. They're functionally equivalent.

In the terminology, RIGHT or LEFT specify which side of the join always has a record, and the other side might be null. So in a P LEFT OUTER JOIN S, P will always have a record because it's on the LEFT, but S could be null.

To clarify, I guess I'm saying that terminology doesn't matter, as it's only there to help visualize. What matters is that you understand the concept of how it works.

RIGHT vs LEFT

I've seen some confusion about what matters in determining RIGHT vs LEFT in implicit join syntax.

LEFT OUTER JOIN

SELECT *
FROM A, B
WHERE A.column = B.column(+)

RIGHT OUTER JOIN

SELECT *
FROM A, B
WHERE B.column(+) = A.column

All I did is swap sides of the terms in the WHERE clause, but they're still functionally equivalent. (See higher up in my answer for more info about that.) The placement of the (+) determines RIGHT or LEFT. (Specifically, if the (+) is on the right, it's a LEFT JOIN. If (+) is on the left, it's a RIGHT JOIN.)

Types of JOIN

The two styles of JOIN are implicit JOINs and explicit JOINs. They are different styles of writing JOINs, but they are functionally equivalent.

Implicit JOINs simply list all tables together. The join conditions are specified in a WHERE clause.

Implicit JOIN

SELECT *
FROM A, B
WHERE A.column = B.column(+)

Explicit JOINs associate join conditions with a specific table's inclusion instead of in a WHERE clause.

Explicit JOIN

SELECT *
FROM A
LEFT OUTER JOIN B ON A.column = B.column

These
Implicit JOINs can be more difficult to read and comprehend, and they also have a few limitations since the join conditions are mixed in other WHERE conditions. As such, implicit JOINs are generally recommended against in favor of explicit syntax.

Could you add examples for non-explicit FROM A, B situations? Let's say, in FROM A, B WHERE a.x = b.x (+) and FROM A, B WHERE b.x (+) = a.x, is the former a right and the latter a left join in your terminology (because of the location of the +), or are they both right joins because B is on the right?
–
Kerrek SBJul 2 '11 at 20:34

@Kerrek SB The ones in the original question are, as are the ones at the link in my answer above. Would you like additional ones?
–
WiseguyJul 2 '11 at 20:35

2

Right, I get it now -- the JOIN is created implicitly by the presence of the (+). Cool.
–
Kerrek SBJul 2 '11 at 20:37

2

@Mike: That's the way the + syntax works. It means "optional", so read it like "List all parts, optionally make the supplier match".
–
Kerrek SBJul 2 '11 at 20:59

2

@Mike: As long as you know what you're selecting, it doesn't really matter how you call it. But do yourself a favour and use the idiomatic JOIN syntax instead! Then there is no room for confusion.
–
Kerrek SBJul 2 '11 at 21:01

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:

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.

Notice here that it's the reverse of what is posted above. I suppose it's possible for this book to have errata, however I trust this book more so than what is in this thread. It's an exam guide for crying out loud...

This contradicts what I linked to in my answer, which is an excerpt from Oracle Database 10g SQL (Osborne ORACLE Press Series) 1st edition (February 20, 2004), stating: "In a left outer join, the outer join operator is actually on the right of the equality operator." Here is a demo of your example. The results of a.col_1(+) = b.col_1 match RIGHT JOIN, not LEFT JOIN.
–
WiseguySep 30 '14 at 21:26