Friday, November 16, 2012

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);

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect