DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: left/right outer join

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    1

    Exclamation

    Hi,
    can you help me to find the equivalent statement?
    I have

    select p.part_id, s.supplier_name
    from part p, supplier s
    where p.supplier_id = s.supplier_id (+);

    the equivalent statement is

    select p.part_id, s.supplier_name
    from part p left outer join supplier s
    on p.supplier_id = s.supplier_id;

    or

    select p.part_id,s.supplier_name
    from supplier s right outer join part p
    on s.supplier_id = p.supplier_id ;



  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    If you want some help, explain exactly what you want the results to look like

  3. #3
    Join Date
    May 2002
    Posts
    108
    Both the statements which you have provided are correct !

    The trick to remember is :

    If the table from which you require all data regardless of the join, is on the left then use LEFT OUTER JOIN, if it is on the right then use RIGHT OUTER JOIN.


    In your query, PART is the table from which you require all data

    1) In statement one PART table is on left(PART mentioned first after the FROM clause). So it LEFT OUTER JOIN

    2) In statement two PART table is on the RIGHT(SUPPLIER mentioned first after the FROM clause ). So it should be RIGHT OUTER JOIN

    Dont confuse the above example while using more than two tables. It is just an hint to remember.

    Note : The compiler how ever flips the RIGHT or LEFT OUTER joins and reorganizes it to LEFT OUTER JOIN. It is only for users flexibility two joins are provided.

    Hope this helps !

    Cheers
    Nandu


    Never give up !

    Nanda Kumar - Vellore

Posting Permissions

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


Click Here to Expand Forum to Full Width