Results 1 to 7 of 7

Thread: outer join

  1. #1
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21

    Question Unanswered: outer join

    hi,

    have the following join ex.:

    SELECT
    R.sel ,
    FK.selfld,
    name, num
    FROM
    (tabfelds RIGHT OUTER JOIN tabrels as R on tabfelds.selfld = R.selfld),
    (tabfelds RIGHT OUTER JOIN tabsdeps as FK on tabfelds.selfld = FK.selfld)

    PS. name,num are fields of tabfelds

    but this does not seem to be possible, (ERROR: tabfelds is already defined)

    how do get the fields of the same table that I use in both joins??

    thanks!

  2. #2
    Join Date
    Dec 2002
    Posts
    15
    FROM
    tabfelds RIGHT OUTER JOIN tabrels as R on tabfelds.selfld = R.selfld RIGHT OUTER JOIN tabsdeps as FK on tabfelds.selfld = FK.selfld

  3. #3
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21

    inner join performance?

    Thanks! did the trick.

    Another question whiel we#re at it about joins...

    in oracle, an

    select a,b
    from taba, tabb
    where a=b

    is an implicit inner join and thus "performance optimised".
    Is this the case of Postgresql as well??

  4. #4
    Join Date
    Dec 2002
    Posts
    15
    Yes, implicit joins are optimized. The difference between implicit and explicit join syntax is the latter bypasses the query optimizer. I have had performance gains by using explicit joins, but it can be limiting - if the tables statistics change considerably, you may have to change the queries.
    Last edited by ultraslacker; 03-18-03 at 14:19.

  5. #5
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21
    ...what I actually meant was, was if

    select aaa.depno, aaa.name, bbb.depno
    from aaa, bbb
    where aaa.depno = bbb.depno

    is as performant as an explicit inner join:

    select aaa.depno, aaa.name, bbb.depno
    from aaa inner join bbb on aaa.depno = bbb.depno


    ??
    thanks!

  6. #6
    Join Date
    Dec 2002
    Posts
    15

    Smile

    Yes, I realized what you meant after I posted - haven't had my morning coffee yet!

  7. #7
    Join Date
    Feb 2003
    Location
    Portugal
    Posts
    21
    No prob! i had my share of coffee for the day..

    I had the sql example mixed up anyway..

Posting Permissions

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