This is one of the areas that many students make a mistake and it is perhaps one of the most frequently asked question in interviews. So study it carefully.
In this type of join the query returns
all rows from one table and selected number of rows from the second table.
For example say we want to find all employees from emp table and their deprtname from dept table. However the problem is some of the employees have a department id that does not exist in dept table. In such a situation we will use outer join in the SQL query.
The structure of emp table is
| eno |
ename |
mgrno |
dept_id |
| Employee No |
Employee Name |
Manager Number |
Department id |
Data in the table is
| eno |
ename |
mgrno |
dept_id |
| 1 |
suku |
2 |
10 |
| 2 |
pari |
3 |
30 |
The structure of dept table is
| dept_id |
dname |
| Department id |
Departtment Name |
Data in the table is
| dept_id |
dname |
| 10 |
Physics |
| 20 |
Chemistry |
Now run the following query
Select e.ename, d.dname
from emp e, dept d
where e.dept_id=d.dept_id(+)
This will return
| ename |
dname |
| suku |
Physics |
| pari |
|
Please see that even though pari does not have a matching record in dept the record from emp table is returned.