Joins.sql

--> CORTITION JOIN
SELECT ename ,dname,loc,e.deptno,empno,sal,grade,losal,mgr FROM dept d,emp e,salgrade s;

--> EQUI JOIN
SELECT ename ,d.deptno, dname, job,loc FROM emp e,dept d WHERE e.deptno=d.deptno;
SELECT ename ,empno,job,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=&eno ;
SELECT ename ,empno,job,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno AND job=Upper('clerk');
SELECT ename ,empno,job,dname,loc,e.deptno FROM emp e,dept d WHERE e.deptno=d.deptno AND job=Upper(&empno);

--> SELF JOIN
select e.ename,m.ename,e.mgr,m.mgr from emp e,emp m where e.empno=m.mgr;

--> RIGHT//LEFT OUTR JOIN
select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno=d.deptno(+);
select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno(+)=d.deptno;
select e.ename,d.deptno,e.mgr from emp e,dept d where e.deptno=d.deptno(+) ORDER BY deptno;
SELECT e.ename,Nvl(m.ename,'supreme authority') FROM emp e,emp m WHERE e.mgr(+) =m.empno;

--> JOIN MORE THAN ONE TABLES
SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal"
FROM emp e,dept d,emp m,salgrade se,salgrade sm
WHERE (e.deptno=d.deptno) AND (e.mgr=m.empno) AND (e.sal between se.losal AND se.hisal) AND (m.sal BETWEEN sm.losal AND sm.hisal);

-->JOIN MORE THAN ONE TABLE
SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal" FROM emp e,dept d,emp m,salgrade se,salgrade sm WHERE (e.deptno=d.deptno) AND (e.mgr=m.empno) AND (e.sal between se.losal AND se.hisal) AND (m.sal BETWEEN sm.losal AND sm.hisal);

-->NATURAL JOIN
SELECT ename,deptno,empno,dname,sal,loc FROM emp NATURAL join dept;
SELECT ename,deptno,empno,sal,grade FROM emp NATURAL join salgrade ;

-->USING CLAUSE
SELECT e.ename,d.dname,deptno FROM emp e join dept d USING(deptno);

-->INNER JOIN
SELECT e.ename,e.deptno,d.dname,loc,sal FROM emp e INNER JOIN dept d ON(d.deptno=e.deptno);
SELECT e.empno,e.ename,m.ename,e.mgr FROM emp e inner join emp m ON(e.mgr=m.empno) ORDER BY e.sal desc;
SELECT e.ename,job,d.deptno,d.dname,e.sal,grade,losal,hisal FROM emp e inner join dept d ON(e.deptno=d.deptno) inner join salgrade ON(e.sal BETWEEN losal AND hisal);
SELECT e.ename,m.ename "manager name",d.dname "department name",e.sal "Employe Salary",se.grade "Employee Grade",m.sal "Manager Sal",sm.grade "Manager sal" FROM emp e inner join dept d on(e.deptno=d.deptno) inner join emp m on(e.mgr=m.empno) inner join salgrade se on(e.sal between se.losal AND se.hisal) inner join salgrade sm on(m.sal BETWEEN sm.losal AND sm.hisal);

-->LEFT//RIGHT//FULL OUTER JOIN
SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e LEFT OUTER JOIN dept d ON(e.deptno=d.deptno);
SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e right OUTER JOIN dept d ON(e.deptno=d.deptno);
SELECT e.ename,e.mgr,d.dname,d.deptno FROM emp e full OUTER JOIN dept d ON(e.deptno=d.deptno);