Re: outer join type syntax

Hello,

"Jean Legare" <jglegare_at_hotmail.com> wrote in message news:68e7005a.0404131020.5abbc448_at_posting.google.com...
> Have come across some Oracle syntax that I have not seen used before.
> I have to translate the SQL so that It will work on Sybase Adaptive
> Server Enterprise.
>
> The Oracle syntax is table. field_name (+) = 'X'
>
> To me this looks like an outer join.
>
> Is this what it is? What does it do, bring back NULLS for those
> records where the field is not 'X'?

This syntax allows you to select only those rows from the non-preserved table that satisfy the predicate _before_ (or rather during the time) the rows are joined to the preserved table.

Consider this:

create table t1(id int);
create table t2(id int);

insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t2 values(2);
insert into t2 values(3);

• In Oracle 8i, a left outer join would look like this:

select * from t1,t2 where t1.id=t2.id(+) -- result

1,null
2,2
3,3

• In Sybase, the equivalent would be:

select * from t1,t2 where t1.id*=t2.id

Now, if you wanted to join t1 only to the row in t2 where id equals, say, 2, you'd use this in Oracle 8i:

select * from t1,t2 where t1.id=t2.id(+) and 2=t2.id(+) --result

1,null
2,2
3,null

You cannot just say

select * from t1,t2 where t1.id=t2.id(+) and 2=t2.id

because Oracle will apply the second predicate _after_ the join (conceptually) and you'll get
-- result
2,2

In Sybase, however, the following syntax will work in the way as Oracle's 'select * from t1,t2 where t1.id=t2.id(+) and 2=t2.id(+)':

select * from t1,t2 where t1.id*=t2.id and t2.id=2 -- result

1,null
2,2
3,null

If your Oracle and Sybase are young enough, you can use the ANSI-92 notation for both databases which is much better for outer joins since it eliminates confusion and ambiguity (you do not need to think whether the predicate is applied during or after the the join, etc.):

select * from t1 left join t2 on t1.id=t2.id and t2.id=2 -- the predicates are applied during the join
-- result

1,null
2,2
3,null

select * from t1 left join t2 on t1.id=t2.id where t2.id=2 -- the second predicate is applied after the join
-- result
2,2

VC

> J
Received on Tue Apr 13 2004 - 17:13:52 CDT