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.