Re: outer join precedence

On Sat, 15 Dec 2001 13:55:31 +0100, "Yucheng SHA" <yucheng.sha_at_netcourrier.com> wrote:

>
>I have asked a question in Microsoft new group :
>
>
>// I tried a cascade outer join by the following query :
>//
>// select U.userid, u.name , i.itemno , b.bid
>// from users u, items i , bids b
>// where u.userid *= i.offered_by and
>// i.itemno *= b.itemno
>// ;
>//
>// and I got this error messsage :
>//
>// Server: Msg 301, Level 16, State 1, Line 1
>// Query contains an outer-join request that is not permitted.
>//
>// SQL Server do not support cascade outer join ?
>
>and I have have a answer :
>
>// Using the *= or the =* join operator, you can't use a single
>column as both an inner and an outer member. The problem has to do with
>getting different results based on join precedence.
>//
>// The correct way to do it is using the SQL-92 LEFT JOIN syntax,
>like:
>//
>// SELECT u.userid, u.nam, i.itemno, b.bid
>// FROM bids AS b
>// LEFT JOIN items AS i
>// ON (i.itemno = b.itemno)
>// LEFT JOIN [users] AS u
>// ON (u.userid = i.offered_by)
>// ;
>
>In oracle SQL-92 LEFT JOIN syntax is not supportted , how does oracle
>decide
>the join precedence ? is there the problem of "different results" in oracle
>?
>
>tia
>.com
>
>

Oracle 9i supports the syntax, I believe. 8i does not. Joins default to INNER JOINs unless you specifically apply the (+) operator, which turns it into an OUTER JOIN. A FULL OUTER JOIN is not availible in 8i.

There should be no issues with different results. The join order should give the same results, unless you try an INNER JOIN on a table that has already been OUTER JOINed, and, in that case, Oracle will throw an error.

Brian Received on Fri Dec 14 2001 - 08:56:01 CST