Diff between Outer and inner Join

[tomate]

Hello
i have a simple question.
What is different zwischen an outer join and an inner join (equi-joins). The outer join is clear beause there is an + in the where clause.
Give me some example

[Edited by tomate on 06-28-2001 at 06:51 AM]

[pipo]

suppose you have 2 tables :
departments (dept_id, dept_name)
containing :
10,MATHS
20,FRENCH
30,ORACLE
and teachers (name, dept_id)
containing :
SMITH,10
PIPO,30

if you do the following query, which uses a simple join (or inner join) :

select dept_name, name
from departements, teachers
where departements.dept_id = teachers.dept_id;

you will have only the lines that meet the criteria, that is to say that you'll have :

MATHS SMITH
ORACLE PIPO

but you do not see the departement 20.
if you want to see it whereas there is a corresponding teacher or not, you'll use an outer join :

select dept_name, name
from departements, teachers
where departements.dept_id = teachers.dept_id(+);

so now if there is a departement that does not meet the criteria, dept_name will be returned, and name will be NULL ...
you'll get the result :

MATHS SMITH
FRENCH
ORACLE PIPO

this is the difference between a join (or inner join) and an outer join

[vbaskar]

Hi

inner Join !!! I DONN think there is an terminology like that...
In a join, one row set is called inner, and the other is called outer..so if u specify the (+) then it means that there is a strech in the data for the Join..so it is just an simple join and outer join..

[pipo]

exact, but people who worked with Access use the term 'inner join' instead of simple join

[vbaskar]

oh yeah...i wasnt working on access !! sorry !!

[sonaliak]

There are lot of threads in this forum which discusses this.. Please search this forum for outer join !

thanks

[chrisrlong]

Well, it's not just Access - it's called SQL-92 standard SQL and Oracle is simply a little late implementing in - like an entire Decade!!!! But don't worry - you will become familiar with it soon enough, as they are finally supporting the standard in 9i.

- Chris

[tomate]

Thanks for the help.
The best information was that an inner join is a simple join.
I think i have something missed but it is an simple join.
About this thematik i have learn much but i'm not known somethin about inner join. Ok it comes from access.

Thanks for the very good help