[Olusoga]

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

[r937]

use a LEFT OUTER JOIN with an IS NULL test on the right join column

[Olusoga]

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

[chuck_forbes]

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

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

[r937]

even more important is having more than one arrow in your quiver

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

[r937]

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

[beilstwh]

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 (+)

[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.
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

[anacedent]

Select Key From Table_a
Minus
Select Key From Table_b

[r937]

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

[Olusoga]

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

[chuck_forbes]

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