| Outer Join Table [message #373449] |
Tue, 17 April 2001 16:33  |
Jini
Messages: 7
Registered: April 2001
|
Junior Member |
|
|
Can someone explain me whats outer join table and why and when it is used?
Also, can someone give me a good example of it?
Thank you,
Jini
|
|
|
|
| Re: Outer Join Table [message #373451 is a reply to message #373449] |
Tue, 17 April 2001 18:16  |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hi,
Whereas an exact join between one or more tables
will give you all the rows satisfying that where condition,
outer join will go a little further.
For example you have DEPT & EMP Tables in Scott/Tiger schema which store Deptno, Dept. Name
in Dept Table& Empno,Deptno,Ename sal etc in Emp table.
So
select a.deptno,a.dname,b.ename,b.sal
from dept a, emp b
where a.deptno = b.deptno
order by a.deptno
will give you all the departments and the employees who are in that dept.
Suppose you have an employee who joined organisation and yet to be inducted into a Dept
(say a trainee). Remember a Foreign Key column can be null.
So if u want every employee in the organisation
who is on pay roll(no matter whether he belongs to some dept or not) above SELECT statement will be modified as :
select a.deptno,a.dname,b.ename,b.sal
from dept a, emp b
where a.deptno = b.deptno(+)
order by a.deptno
Which will return that trainee.
well, the gist is OUTER JOIN gives a way to ignore the exactness for those rows whose column values are null.
Madhav
|
|
|
|
|
|
| Re: Outer Join Table [message #373460 is a reply to message #373451] |
Wed, 18 April 2001 08:48 |
aish74
Messages: 19
Registered: March 2001
|
Junior Member |
|
|
and further if you want only the trainee
I would extend the SQL to
select a.deptno,a.dname,b.ename,b.sal
from dept a, emp b
where a.deptno = b.deptno(+)
and b.deptno is null
Ans can be used in verifications
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
