How to Use the SQL Outer Join

When you’re joining two SQL tables, the first one (call it the one on the left) may have rows that don’t have matching counterparts in the second table (the one on the right). Conversely, the table on the right may have rows that don’t have matching counterparts in the table on the left.

If you perform an inner join on those tables, all the unmatched rows are excluded from the output. Outer joins, however, don’t exclude the unmatched rows. Outer joins come in three types: the left outer join, the right outer join, and the full outer join.

Left outer join

In a query that includes a join, the left table is the one that precedes the keyword JOIN, and the right table is the one that follows it. The left outer join preserves unmatched rows from the left table but discards unmatched rows from the right table.

To understand outer joins, consider a corporate database that maintains records of the company’s employees, departments, and locations.

LOCATION_ID CITY
1 Boston
3 Tampa
5 Chicago

Here the company tracks the departments.

DEPT_ID LOCATION_ID NAME
21 1 Sales
24 1 Admin
27 5 Repair
29 5 Stock

Here the company tracks the employees.

EMP_ID DEPT_ID NAME
61 24 Kirk
63 27 McCoy

Now suppose you want to see all the data for all employees, including department and location. You get this with an equi-join:

SELECT *
 FROM LOCATION L, DEPT D, EMPLOYEE E
 WHERE L.LocationID = D.LocationID
  AND D.DeptID = E.DeptID ;

This statement produces the following result:

1 Boston 24 1  Admin 61 24 Kirk
5 Chicago 27 5  Repair 63 27 McCoy

This result table gives all the data for all the employees, including location and department. The equi-join works because every employee has a location and a department.

Next, suppose you want the data on the locations, with the related department and employee data. This is a different problem because a location without any associated departments may exist. To get what you want, you have to use an outer join, as in the following example:

SELECT *
 FROM LOCATION L LEFT OUTER JOIN DEPT D
  ON (L.LocationID = D.LocationID)
 LEFT OUTER JOIN EMPLOYEE E
  ON (D.DeptID = E.DeptID);

This join pulls data from three tables. First, the LOCATION table is joined to the DEPT table. The result set is then joined to the EMPLOYEE table. Rows from the table on the left of the LEFT OUTER JOIN operator that have no corresponding row in the table on the right are included in the result.

Thus, in the first join, all locations are included, even if no department associated with them exists. In the second join, all departments are included, even if no employee associated with them exists. The result is as follows:

1 Boston 24 1 Admin 61 24 Kirk
5 Chicago 27 5 Repair 63 27 McCoy
3 Tampa  NULL NULL NULL  NULL NULL NULL
5 Chicago 29 5 Stock NULL NULL NULL
1 Boston 21 1 Sales NULL NULL NULL

The first two rows are the same as the two result rows in the previous example. The third row (3 Tampa) has nulls in the department and employee columns because no departments are defined for Tampa and no employees are stationed there.

The fourth and fifth rows (5 Chicago and 1 Boston) contain data about the Stock and the Sales departments, but the Employee columns for these rows contain nulls because these two departments have no employees. This outer join tells you everything that the equi-join told you — plus the following:

  • All the company’s locations, whether they have any departments or not

  • All the company’s departments, whether they have any employees or not

The rows returned in the preceding example aren’t guaranteed to be in the order you want. The order may vary from one implementation to the next. To make sure that the rows returned are in the order you want, add an ORDER BY clause to your SELECT statement, like this:

SELECT *
 FROM LOCATION L LEFT OUTER JOIN DEPT D
  ON (L.LocationID = D.LocationID)
 LEFT OUTER JOIN EMPLOYEE E
  ON (D.DeptID = E.DeptID)
 ORDER BY L.LocationID, D.DeptID, E.EmpID;

You can abbreviate the left outer join language as LEFT JOIN because there’s no such thing as a left inner join.

Right outer join

The right outer join preserves unmatched rows from the right table but discards unmatched rows from the left table. You can use it on the same tables and get the same result by reversing the order in which you present tables to the join:

SELECT *
 FROM EMPLOYEE E RIGHT OUTER JOIN DEPT D
  ON (D.DeptID = E.DeptID)
 RIGHT OUTER JOIN LOCATION L
  ON (L.LocationID = D.LocationID) ;

In this formulation, the first join produces a table that contains all departments, whether they have an associated employee or not. The second join produces a table that contains all locations, whether they have an associated department or not.

You can abbreviate the right outer join language as RIGHT JOIN because there’s no such thing as a right inner join.

Full outer join

The full outer join combines the functions of the left outer join and the right outer join. It retains the unmatched rows from both the left and the right tables. Consider the most general case of the company database used in the preceding examples. It could have

  • Locations with no departments

  • Departments with no locations

  • Departments with no employees

  • Employees with no departments

To show all locations, departments, and employees, regardless of whether they have corresponding rows in the other tables, use a full outer join in the following form:

SELECT *
 FROM LOCATION L FULL OUTER JOIN DEPT D
  ON (L.LocationID = D.LocationID)
 FULL OUTER JOIN EMPLOYEE E
  ON (D.DeptID = E.DeptID) ;

You can abbreviate the full-outer-join language as FULL JOIN because there’s no such thing as a full inner join.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com