Q
Problem solve Get help with specific problems with your technologies, process and projects.

What is an outer join?

What is an outer join? How is it advantageous over an inner join?

What is an outer join? How is it advantageous over an inner join?
An outer join cannot be considered to have any advantage over an inner join or vice versa. The inner and outer joins just have the opportunity to return different result sets, depending on the two tables to be joined. Which one you use depends on the data you want returned. A simple example will show the differences between the two join operators. Let's assume I have the following tables:
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 20
With 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 White
Notice 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
Sales
As 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.
This was last published in October 2006

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close