Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: Difference between non equi join and outer joins

    Dear all,
    please can someone explain to me the difference between outer joins, non equi join and inner joins in details please. Also kindly give me an idea of the scenarios where they apply.
    For example, I have two tables A and B, their join column is centre that is

    A.centre=B.centre

    Now i want to retrieve records from A that do not have centres in B.
    Which join type do i use here.
    Thanks
    Olusoga

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use a LEFT OUTER JOIN with an IS NULL test on the right join column


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Posts
    29

    difference between outer join and non eui join

    Please i forgot to mention that I am running oracle 10g database.
    Also where does this outer join operator (+) come into play.
    I don't seem to get the left outer join with IS null solution.
    Thanks

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    In my opinion, a subselect is more appropriate here. I think it communicates your desire in finding recs from one table which don't exist in the other

    Code:
    select *
    from   tabl1 A
    where  not exists (select 1 
                       from   tabl2 B
                       where  A.centre=B.centre)
    An outer join, again in my opinion, is better reserved for cases where you are interested in seeing columns from the inner table.

    --=cf

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    even more important is having more than one arrow in your quiver
    Code:
    select A.*
      from A
    left outer
      join B
        on B.centre = A.centre
     where B.centre is null
    you never know when one of them will outperform the other

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Olusoga
    Also where does this outer join operator (+) come into play.
    it should never, not ever, ever again

    that horrid syntax is from the last millenium and you should be using explicit JOIN syntax instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by r937
    it should never, not ever, ever again

    that horrid syntax is from the last millenium and you should be using explicit JOIN syntax instead

    Using the (+) join in oracle has always worked and up until oracle 10G, the JOIN syntax has had a number of bugs. However, there are things that the join syntax can do that the old (+) could never do such as outer joining to more then one table. In 10G and above, I would use JOIN, before I would use (+)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Nov 2007
    Posts
    29

    Thumbs down outer join

    Hi
    What the left outer join seems to be doing is to retrieve the rows that satisy the join condition as well as the rows from Table A where column values is null in B.
    What I want is ONLY the rows in table A that are not present in table B based on their join condition. I do not want values that satisfy the join condition at all.
    Please help out
    Thanks a million
    \'SOga

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select Key From Table_a
    Minus
    Select Key From Table_b
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Olusoga
    Hi
    What the left outer join seems to be doing is to retrieve the rows that satisy the join condition as well as the rows from Table A where column values is null in B.
    then you do not understand the LEFT OUTER JOIN with the IS NULL condition

    what the LEFT OUTER JOIN is actually doing is trying to find rows from B that satisfy the join condition, and if none are found, it sets the B columns in the result set to NULL

    this is what the IS NULL condition in the WHERE clause is used for -- to find result rows where the join column from B has been set to NULL because there was no match

    only these result rows are returned -- i.e. this is exactly what you wanted, rows from A which have no matching rows in B
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2007
    Posts
    29

    left outer join

    Thanks r937 and chuck_forbes.
    The two didfferent queries worked fine but i noticed in the query response time that the left outer join query performed faster in (104 secs) than the subquery (189secs). Thanks all

  12. #12
    Join Date
    Dec 2003
    Posts
    1,074
    Did you try executing the same queries repeatedly? Depending upon which one is executed first, as well as other factors, can affect timing. They also may perform differently in your production environment vs dev if you have different data, and thus different statistics. ---=cf

Posting Permissions

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