Posted by Serge Rielau on 06/05/05 07:14

DA Morgan wrote:
> Erland Sommarskog wrote:
>
>> In this example: not very much. But digest this:
>>
<snip>
>> Actually, as long as we are into inner joins, both syntaxes are
>> ANSI-compatible. It is when it comes to outer joins it matter.
>> Here both SQL Server and Oracle have their own propritary operators.
>
>
> Not true. Oracle has for multiple versions now allowed either ANSI or
> ISO syntax.
Not his point. The point is that the prorietary synatx also has
proprietary semantics. So why teach what Oracle discourages?
Interesting to note that Oracle bothered. Apparantly they saw a need
for compliance for core function...
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/queries006.htm
Quote:
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 (that is, 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 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.
*

A WHERE condition cannot compare any column marked with the (+)
operator with a subquery.

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.
Please refer to SELECT for the syntax for an outer join.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Back to original message]