Oracle Join Queries

JOIN QUERIES ARE USED TO RETRIEVE DATA FROM TWO OR MORE TABLES AND DISPLAY THAT DATA IN A SINGLE OUTPUT.

1. EQUI-JOIN

2. OUTER JOIN

3. SELF JOIN

4. NON-EQUI JOIN

5. CARTESIAN JOIN

6. CROSS JOIN

7. NATURAL JOIN

8. NATURAL JOIN WITH USING CLAUSE

9. ON JOIN

10. LEFT OUTER JOIN

11. RIGHT OUTER JOIN

12. FULL OUTER JOIN

EQUI JOIN:- THIS TYPE OF JOIN IS USED ON THOSE TABLES THAT KEEP COMMON COLUMNS CONTAINING SOME COMMON DATA.

DISPLAY EMP DETAILS ALONG WITH CORRESPONDING DEPT DETAILS.

SELECT EMPNO,ENAME,JOB,EMP.DEPTNO,DNAME,LOC

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO;

SELECT EMPNO,ENAME,JOB,EMP.DEPTNO,DNAME,LOC

FROM EMP,DEPT

WHERE DEPT.DEPTNO=EMP.DEPTNO;

SELECT E.EMPNO,E.ENAME,E.JOB,D.DEPTNO,D.DNAME,D.LOC

FROM EMP E,DEPT D

WHERE D.DEPTNO=E.DEPTNO;

OUTER JOIN:- THE OUTER JOIN IS SIMILAR TO THE EQUI JOIN BUT IT WILL GET THAT EXTRA DATA THAT IS AVAILABLE IN ONLY ONE OF THE TWO TABLES. THIS EXTRA DATA IS DISPLAYED ALONG WITH ALL OTHER DATA FROM BOTH TABLES.

DISPLAY EMP DETAILS ALONG WITH CORRESPONDING DEPT DETAILS AND ALSO DISPLAY THAT DEPT''S DETAILS  IN WHICH THERE ARE NO EMPLOYEES.

TO GET THIS EXTRA INFORMATION ORACLE USES THE OUTER JOIN OPERATOR THAT IS (+).

THIS OPERATOR MUST BE PLACED WITHIN THE JOIN CONDITION ON THAT TABLES SIDE THAT IS DEFFICIENT OF THE DATA.

SELECT EMPNO,ENAME,JOB,DEPT.DEPTNO,DNAME,LOC

FROM EMP , DEPT

WHERE EMP.DEPTNO(+)=DEPT.DEPTNO;

SELECT EMPNO,ENAME,JOB,EMP.DEPTNO,DNAME,LOC

FROM EMP , DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO(+);

SELF JOIN:-

THE SELF JOIN IS USED ON THOSE TABLES THAT KEEP A PARENT CHILD RELATION WITHIN THE TABLE ITSELF.

DISPLAY EMP DETAILS ALONG WITH MANAGERS NAME AND EMPNO.

SELECT E.EMPNO,E.ENAME,M.EMPNO,M.ENAME

FROM EMP E , EMP M

WHERE M.EMPNO=E.MGR;

DISPLAY THOSE EMP WHO ARE EARNING MORE THAN THEIR MANAGER AND ALSO DISPLAY THE MANAGERS INFORMATION ALONG WITH THE EMP.

SELECT E.EMPNO,E.ENAME,E.SAL,M.EMPNO,M.ENAME

FROM EMP E , EMP M

WHERE M.EMPNO=E.MGR

AND E.SAL>(SELECT J.SAL FROM EMP J

                    WHERE J.EMPNO=E.MGR);

NON-EQUI JOIN

IT IS USED ON THOSE TABLES THAT DO NOT KEEP ANY  COMMON COLUMNS. THE NON EQUI JOIN IS BASED ON VALUE RANGE COMPARISONS.

DISPLAY ALL EMP DETAILS ALONG WITH SALGRADE DETAILS.

SELECT EMPNO,ENAME,SAL,GRADE

FROM EMP ,SALGRADE

WHERE SAL BETWEEN LOSAL AND HISAL;

SELECT EMPNO,ENAME,SAL,GRADE

FROM EMP,SALGRADE

WHERE SAL>=LOSAL AND SAL<=HISAL;

DISPLAY THOSE WHOSE GRADE IS SAME AS MANAGERS GRADE.

SELECT E.EMPNO,E.ENAME,S.GRADE,K.EMPNO,K.ENAME

FROM EMP E , SALGRADE S, EMP K

WHERE (E.SAL BETWEEN S.LOSAL AND S.HISAL)

AND S.GRADE=(SELECT M.GRADE

          FROM EMP J,SALGRADE M

          WHERE (J.SAL BETWEEN M.LOSAL AND M.HISAL)

          AND J.EMPNO=E.MGR)

AND K.EMPNO=E.MGR;

  

CARTESIAN JOIN:- THIS TYPE OF JOIN WILL NOT KEEP ANY JOIN CONDITION , IT WILL SIMPLY DISPLAY ALL DATA FROM TWO OR MORE TABLES THAT HAVE BEEN MENTIONED IN THE QUERY.

SELECT EMPNO,ENAME,DEPT.DEPTNO,DNAME FROM EMP,DEPT

THE CROSS JOIN PRODUCES THE CROSS PRODUCT OF TWO TABLES. IT IS THE SAME AS CARTESIAN JOIN.

SELECT ENAME,EMP.DEPTNO,DNAME FROM

EMP CROSS JOIN DEPT;

SELECT ENAME , DNAME FROM

DEPT CROSS JOIN EMP ;

NATURAL JOIN:-

THE NATURAL JOIN IS BASED ON ALL COLUMNS OF TWO TABLES THAT HAVE THE SAME COLUMN NAME.

IF THE COLUMNS HAVING THE SAME NAME HAVE DIFFERENT DATA TYPES THEN THE NATURAL JOIN WILL RETURN AN ERROR.

SELECT EMPNO,ENAME,DEPTNO,DNAME,LOC

FROM EMP NATURAL JOIN DEPT;

NATURAL JOIN WITH USING CLAUSE:-

INCASE THE COLUMNS HAVE SAME NAME BUT DIFFERENT DATA TYPES THEN WE CAN USE THE NATURAL JOIN WITH THE USING CLAUSE.

SELECT EMPNO,ENAME,DEPTNO,DNAME,LOC

FROM EMP JOIN DEPT USING (DEPTNO);

ON JOIN:-

THIS TYPE OF JOIN LETS US SPECIFY THE JOIN CONDITION SEPERATELY FROM THE SEARCH OR FILTER CONDITION, THERE BY INCREASING THE PERFORMANCE OF THE QUERY

SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC

FROM EMP E JOIN  DEPT D

ON(E.DEPTNO=D.DEPTNO)

WHERE D.DNAME LIKE '%S';

LEFT OUTER JOIN

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROM DEPT D LEFT OUTER JOIN EMP E

ON(E.DEPTNO=D.DEPTNO);

RIGHT OUTER JOIN

SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC

FROM DEPT D RIGHT OUTER JOIN EMP E   

ON(E.DEPTNO=D.DEPTNO);

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC

FROM EMP E , DEPT D

WHERE E.DEPTNO=D.DEPTNO(+)

UNION

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME,D.LOC

FROM EMP E , DEPT D

WHERE E.DEPTNO(+)=D.DEPTNO;

FULL OUTER JOIN

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DEPTNO,D.DNAME

FROM EMP E FULL OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO);