JOINING

CARTESIAN PRODUCT

SELECT * FROM EMP,DEPT;

------------------------------------------------------------------------------

ORACLE 8I AND PRIOR

------------------------------------------------------------------------------

EQUI JOIN

SQL>SELECT EMP.ENAME,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

SQL>SELECT EMP.ENAME,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

AND ENAME='ALLEN'

-------------

USING TABLE ALIASES

SELECT E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO

AND ENAME='ALLEN'

------------

NON-EQUIJOIN

SELECT E.ENAME,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

------------

OUTERJOIN (Place the outer join symbol following the name of the column in the table without the matching rows)

SQL>SELECT e.ENAME, D.DNAME

FROM emp e, dept d

WHERE e.deptNO(+) = d.deptNO

SELECT e.ENAME, D.DNAME

FROM emp e, dept d

WHERE e.deptNO= d.deptNO(+)

-------------------

SELFJOIN

SQL>SELECT WORKER.ENAME ||' WORKS FOR '|| MANAGER.ENAME FROM EMP WORKER,EMP MANAGER WHERE WORKER.EMPNO=MANAGER.EMPNO

------------------------------------------------------------

SQL-1999 COMPLIENT JOIN

------------------------------------------------------------

CROSS JOIN(SIMILER TO CARTESIAN JOIN)

SELECT ENAME,DNAME FROM EMP CROSS JOIN DEPT;

------------

NATURAL JOIN(SIMILAR AS EQUI JOIN)

SELECT ENAME,DNAME FROM EMP NATURAL JOIN DEPT

------------

USING CLAUSE(WHEN TWO OR MORE COLUMN IS SIMILER THEN "USING" CLAUSE POINTS TO THE PERTICULAR)

SELECT E.ENAME,D.DNAME FROM EMP E JOIN DEPT D USING(DEPTNO);

------------

ON CLAUSE

SELECT E.ENAME,D.DNAME

FROM EMP E JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

THREE WAY JOIN USING "ON" CLAUSE

SELECT employee_id, city, department_name

FROM employees e

JOIN departments d

ON d.department_id = e.department_id

JOIN locations l

ON d.location_id = l.location_id;

------------

LEFT OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP E

LEFT OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

RIGHT OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP E

RIGHT OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------

FULL OUTER JOIN

SELECT E.ENAME,D.DNAME

FROM EMP E

FULL OUTER JOIN DEPT D

ON (E.DEPTNO=D.DEPTNO)

------------