Self-Joins Using the ON Clause

• SELECT e.ename as emp, m.ename as mgr FROM emp e JOIN emp m ON (e.mgr = m.empno);

• 

Applying Additional Conditions to a Join :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno) AND e.mgr = 7698 ;
  

Creating Joins with the ON Clause

• The join condition for the natural join is basically an equijoin of all columns with the same name
• Use the ON clause to specify arbitrary conditions or specify columns to join
• The join condition is separated from other search conditions
• The ON clause makes code easy to understand

Retrieving Records with the ON Clause :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno);

•