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.
