To Using Outter Join In Oracle

Suppose we have two tables called EMPLOYEE & DEPARTMENT like displayed below :

SQL> SELECT * FROM EMPLOYEE;

ID    NAME        SALARY          EMAIL               DEPT_ID    MANAGER_ID
----- ----------- --------------- ------------------  ---------- ------------
1     TEDY        12500000        tedy@apt.co.id      5
2     BUDY        2750000         budy@apt.co.id      1          1
3     EDDY        49750000        eddy@apt.co.id      1          1
4     LILIS       5200000         lilis@apt.co.id     2          2
5     MANUS       1950000         manus@apt.co.id     3          2
6     TONY        2300000         tony@apt.co.id      4          1
7     YANNI       1500000         yanni@apt.co.id     3          2

7 rows selected.

SQL> SELECT * FROM DEPARTMENT;

DEPT_ID  DEPARTMENT_NAME
-------- --------------------
1        Consulting
2        Support
3        Sales
4        Finance
5        Legal

SQL>

Using LEFT OUTER JOIN, all the rows from the table specified on the left side of JOIN keyword will be returned, even if it doesn’t match with any value on the other table. Below is the example of left outer join :

SQL> SELECT e.NAME,e.DEPT_ID,d.DEPT_ID
  2     FROM EMPLOYEE e
  3     LEFT OUTER JOIN department d
  4  	ON e.DEPT_ID = d.DEPT_ID;

NAME         DEPT_ID   DEPT_ID
------------ --------- ----------
EDDY         1         1
BUDY         1         1
LILIS        2         2
YANNI        3         3
MANUS        3         3
TONY         4         4
TEDY         6

7 rows selected.

SQL>

Using RIGHT OUTER JOIN, all the rows from the table specified on the right side of JOIN keyword will be returned, even if it doesn’t match with any value on the other table. And below is the example of right outer join :

SQL> SELECT e.NAME,e.DEPT_ID,d.DEPT_ID
  2     FROM EMPLOYEE e
  3  	RIGHT OUTER JOIN department d
  4  	ON e.DEPT_ID = d.DEPT_ID;

NAME         DEPT_ID    DEPT_ID
------------ ---------  ----------
BUDY         1          1
EDDY         1          1
LILIS        2          2
MANUS        3          3
TONY         4          4
YANNI        3          3
                        5

7 rows selected.

SQL>

Instead of left/right outer join, we can use FULL OUTER JOIN like this :

SQL> SELECT e.NAME,e.DEPT_ID,d.DEPT_ID
  2     FROM EMPLOYEE e
  3  	FULL OUTER JOIN department d
  4  	ON e.DEPT_ID = d.DEPT_ID;

NAME         DEPT_ID   DEPT_ID
------------ --------- ----------
EDDY         1          1
BUDY         1          1
LILIS        2          2
YANNI        3          3
MANUS        3          3
TONY         4          4
TEDY         6
                        5

8 rows selected.

SQL>

Using full outer join, all the rows including unmatched rows will be displayed.

Leave a Reply