Re: Code in the database or middle tier (the CLR controversy)

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sun, 05 Jun 2005 00:14:15 -0400
Message-ID: <3gfcguFc44lmU1_at_individual.net>

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 [Quoted] proprietary semantics. So why teach what Oracle discourages? [Quoted] 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

Received on Sun Jun 05 2005 - 06:14:15 CEST