Q: Explain Normal Join

A normal join will look like the following

SELECT e.ename NAME, d.deptname DEPARTMENT
FROM emp e, dept d
WHERE e.deptno = d.deptno;
Here the tables emp and dept have been joined by the column deptno. Please note the use of  table alias (emp e) and column alias (e.ename NAME) in this statement.

 

Explain Concept of Self Join

When a table is joined to itself in a query then that is called a self-join.
The structure of emp table is

eno	ename	mgrno
Employee No	Employee Name	Manager Number

Data in the table is

eno	ename	mgrno
1	suku	2
2	pari	3

Now we want to find the name of the manager of the employee named 'suku'. This can be done by the following query
Select mgr.ename "Manager Name"
 From  emp e, emp mgr
Where e.mgrno=mgr.eno
Here the emp table is joined with itself. So this is a self join.