Joining Tables

1.6  Joining Tables

1.6.1       The INNER Join Clause

·         SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME

         FROM EMP

  INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

1.6.2       The OUTER JOIN Clause

-          LEFT OUTER JOIN – returns all records from the table listed in the FROM clause.

·         SELECT EMP.EMPNO, EMP.ENAME, EMP.MGR,

          MGR.ENAME AS MGR_NAME

          FROM EMP

            LEFT JOIN EMP AS MGR ON EMP.MGR = MGR.EMPNO;

-          RIGHT OUTER JOIN – returns all records from the table listed in the JOIN clause.

·         SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME

   FROM EMP

  RIGHT JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO;