Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    55

    Unanswered: Full outer join??

    hi every1
    can anyone tell me the syntax for full outer join in sybase..
    the usual sql way of doing it doesnt seem to work in sybase
    giving me error...

    tht is saying table1 FULL OUTER JOIN table2 ON x ..etc doesnt wrk with sybase, i get a syntax error near FULL when i give something like

    from table1 T1
    FULL OUTER JOIN
    table2 T2
    ON T1.x=T2.x

    is there somethning wrong with my syntax or sybase doesnt support full outer join. if not then is there any other way of doing this?pleez help asap!
    any examples/links wud be gr8!
    thnx

    -shuchi
    Last edited by shuchi; 02-07-03 at 05:45.
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

  2. #2
    Join Date
    Jun 2002
    Location
    Dublin, Ireland
    Posts
    23

    Re: Full outer join??

    Hi Shuchi,

    To do an outer join simply do the following:

    select *
    from table1 T1
    ON T1.x *=T2.x

    Regards,

    Mark


    Originally posted by shuchi
    hi every1
    can anyone tell me the syntax for full outer join in sybase..
    the usual sql way of doing it doesnt seem to work in sybase
    giving me error...

    tht is saying table1 FULL OUTER JOIN table2 ON x ..etc doesnt wrk with sybase, i get a syntax error near FULL when i give something like

    from table1 T1
    FULL OUTER JOIN
    table2 T2
    ON T1.x=T2.x

    is there somethning wrong with my syntax or sybase doesnt support full outer join. if not then is there any other way of doing this?pleez help asap!
    any examples/links wud be gr8!
    thnx

    -shuchi

  3. #3
    Join Date
    Jun 2002
    Location
    Dublin, Ireland
    Posts
    23

    Re: Full outer join??

    oops left out Table 2.

    select *
    from table1 T1 , table2 T2
    ON T1.x *=T2.x


    Originally posted by msheehan
    Hi Shuchi,

    To do an outer join simply do the following:

    select *
    from table1 T1
    ON T1.x *=T2.x

    Regards,

    Mark
    Mark Sheehan,
    Senior Sybase DBA

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, but that's just a left join, msheehan -- or is it a right join? i can never remember where the asterisk goes and which table has the nulls

    in any case, sybase does support LEFT/RIGHT OUTER JOIN syntax, so i would always use that, instead of what they cal' "transact-sql" syntax, that confounding asterisk

    however, that's still not a full outer join

    a full outer can be achieved by a left outer join and a right outer join combined with a UNION

    select T1.foo, T2.bar
    from table1 T1
    left outer join table2 T2
    on T1.x= T2.x
    UNION
    select T1.foo, T2.bar
    from table1 T1
    right outer join table2 T2
    on T1.x= T2.x

    you may suspect that rows which match will be returned by both of the queries in this union, and you'd be right

    the UNION will take care of removing these duplcates

    if you wish to avoid the sort that UNION requires, use UNION ALL and then take steps to ensure that the matching rows are returned by only one of the two queries in the union

    for example

    select T1.foo, T2.bar
    from table1 T1
    left outer join table2 T2
    on T1.x= T2.x
    UNION ALL
    select T1.foo, T2.bar
    from table1 T1
    right outer join table2 T2
    on T1.x= T2.x
    where T1.x is null

    rudy
    http://r937.com/

  5. #5
    Join Date
    Jan 2003
    Posts
    55

    thnx!:):)

    thanx a million all for the much much needed help....
    ...

    -shuchi
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •