Joins and Types

Here we are giving sample queries for below types of joins

1. Equi Join
2. Non Equi Join
3. Self Join
4. Outer join

------------------ Equi Joins ------------
SELECT Emp.Empno,
  Emp.Ename,
  Emp.Deptno,
  Dept.Deptno,
  Dept.Dname,
  Dept.Loc
FROM Emp,
  Dept
WHERE Emp.Deptno = Dept.Deptno;

SELECT Empno,
  Ename,
  Emp.Deptno,
  Loc
FROM Emp,
  Dept
WHERE Emp.Deptno = Dept.Deptno
AND Job          = UPPER('manager');

SELECT Empno,
  Ename,
  Sal * 12 AnnSal,
  Emp.Deptno,
  Loc
FROM Emp,
  Dept
WHERE Emp.Deptno = Dept.Deptno;

SELECT Dept.Deptno,
  Dname,
  Loc,
  SUM(Sal)
FROM Emp,
  Dept
WHERE Emp.Deptno = Dept.Deptno
GROUP BY Dept.Deptno,
  Dname,
  Loc;

SELECT TO_CHAR(HireDate, 'YYYY') YEAR,
  Dept.Deptno,
  Dname,
  SUM(Sal)
FROM Emp,
  Dept
WHERE Emp.Deptno = Dept.Deptno
GROUP BY TO_CHAR(HireDate, 'YYYY'),
  Dept.Deptno,
  Dname
ORDER BY YEAR;

SELECT E.Empno,
  E.Ename,
  D.Deptno,
  D.Dname
FROM Emp E,
  Dept D
WHERE E.Deptno = D.Deptno;
SELECT E.Ename,
  E.Job,
  D.Deptno,
  D.Dname,
  D.Loc
FROM Emp E,
  Dept D
WHERE E.Deptno = D.Deptno
AND E.Job     IN('ANALYST', 'MANAGER' );

SELECT E.Ename,
  E.Job,
  D.Dname,
  D.Loc
FROM Emp E,
  Dept D
WHERE E.Deptno = D.Deptno
AND D.Dname    < > 'BOSTON';


------ Non Equi Join -----
SELECT E.Ename ,
  E.Sal ,
  S.Grade
FROM Emp E,
  SalGrade S
WHERE E.Sal BETWEEN S.Losal AND S.Hisal;

SELECT E.Ename,
  E.Sal,
  S.Grade
FROM Emp E,
  SalGrade S
WHERE (E.Sal >= S.LoSal
AND E.Sal    <= S.HiSal)
AND S.Grade   = 1;

------- Self Join --------

SELECT E1.Ename "Employee Name",
  E2. Ename "Managers Name"
FROM Emp E1,
  Emp E2
WHERE E1.Mgr = E2.Empno;
SELECT E1.Ename
  ||'''s Managers is '
  || E2.Ename "Employees And Managers"
FROM Emp E1,
  Emp E2
WHERE E1.Mgr = E2.Empno;
SELECT E1.Ename
  ||' Works For '
  || E2.Ename "Employees And Managers"
FROM Emp E1,
  Emp E2
WHERE(E1.Mgr = E2.Empno)
AND E1.Job   = 'CLERK';

------- OUTER JOIN ---------

SELECT E.Ename,
  D.Deptno,
  D.Dname
FROM Emp E,
  Dept D
WHERE E.Deptno (+) = D.Deptno
ORDER BY E.Deptno;

SELECT E.Ename,
  D.Deptno,
  D.Dname
FROM Emp E,
  Dept D
WHERE E.Deptno = D.Deptno(+)
ORDER BY E.Deptno;
SELECT E.Ename Employee,
  NVL(M.Ename, 'Supreme Authoriy') Manager
FROM Emp E,
  Emp M
WHERE E.MGR = M.Empno(+);