Table: DEPT DEPTNO DEPT_NAME 10 Human Resources 20 Accounting 30 Sales Table: EMP EMP_ID ENAME DEPTNO 1001 Bob Jones 10 1002 Sue Smith 10 1003 Joe White 20With an INNER JOIN, we will only get those records that participate in the join from both tables, as can be seen in this example:
SELECT d.dept_name, e.ename FROM dept d INNER JOIN emp e ON d.deptno=e.deptno; DEPT_NAME ENAME Human Resources Bob Jones Human Resources Sue Smith Accounting Joe WhiteNotice that we did not receive any records for the Sales department. That is because there are no employees in that department. Let's change our requirement to return each department, even if they have no employees. The outer join is used for this requirement as can be seen below:
SELECT d.dept_name, e.ename FROM dept d OUTER JOIN emp e ON d.deptno=e.deptno; DEPT_NAME ENAME Human Resources Bob Jones Human Resources Sue Smith Accounting Joe White SalesAs you can see, we now have a record returned for the Sales department even though there are no employees in that department. The ENAME column is from the EMP table and since there was no record to join with the DEPT table for the Sales department, that value is set to NULL in the outer join.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.