Q: Please explain Outer Join in SQL

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.