Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access

June 30, 2005

There are many different kinds of joins, like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The first three of these different join types are implemented in Access, but FULL OUTER JOIN is not. If you need to perform a FULL OUTER JOIN in Access, are you "up a creek, without a paddle"? No, you are not. This article will show you how you can write some SQL code in Access to implement a simulated FULL OUTER JOIN.

What is a FULL OUTER Join?

A FULL OUTER join operation on two record sets returns all of the records contained in both sets even if they do not meet the join criteria. For each record returned that does not have matching records in the corresponding set, a NULL value will be returned for each column selected from the corresponding set.

When you join two tables you can think of the records involved falling into three different unique record sets. The first record set contains only those records meeting the join criteria. This is the set produced when you perform an INNER JOIN. The second set of records is the set of records in the left table that do not meet the join criteria, or are said to not have matching records in the right table. And the last set of records is the set of records in the right table that do not have matching records in the left table. The following diagram illustrates the three different sets:

Click for larger image

In the above diagram, the two tables are represented by the two circles, labeled Table A, and Table B. The three different sets are represented by the three different sections created by the intersection of the two different circles, labeled Set 1, Set 2 and Set 3. Set 1 is the set of records in Table A and B that meet the join criteria. Set 2 is the set of records in Table A that do not match any records in Table B. And Set 3 is the set of records in Table B that do not match any records in Table A. A FULL OUTER join would return a record set that contains all the records from these three different sets 1, 2 and 3.

How to Perform a Simulated FULL OUTER Join in Access

Access does not support the FULL OUTER join clause. Therefore, to simulate the FULL OUTER join functionality you need perform three different joins, and then UNION together the three different result sets. The three different result sets would be similar to the three different sets discussed in the prior section. Where Set 1 would be created using an INNER JOIN, Set 2 would be created with a LEFT OUTER JOIN and Set 3 would be created using a RIGHT OUTER JOIN. To show you how this works lets go though an example using the sample Northwind Access database.

For my example, say I want to perform a FULL OUTER JOIN between the Northwind Employees table and the Suppliers table where I am joining these two tables on the City column. In my final result set I want to display Employees.LastName, Employees.City, Supplies.CompanyName, and the Suppliers.City for all records in both these tables regardless of whether or not there a matching records.

To simulate a FULL OUTER JOIN query I will first create three separate queries, one for each of the sets mentioned above. To do that, my Query Design and Join Properties view for Set 1 would look like this:

Click for larger image

The SQL code behind this design view would look like this:

SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees INNER JOIN Suppliers ON Employees.City = Suppliers.City;

After running this SQL code, I would only return the records for Set 1. Below is the result set for this query:

Click for larger image

As you can see, only four Employees records joined to a single Suppliers record. Notice that this first set was created with an INNER JOIN clause.

The second query, Set 2, needs to perform a LEFT OUTER JOIN between the Employees and Suppliers table. However, I only want to return those records that do not have matching supplier records. Here is the Design and Join Properties view of how I constructed this query:

Click for larger image

As you can see, I have now selected to "Include All records from 'Employees' and only those records from 'Suppliers' where the join fields are equal" for the join property. Although, since I included a criteria that says only return those records where the Suppliers.City is null, I will only return the employee records that do not have a supplier in the same city. Here is the SQL code behind this query:

SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees LEFT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE (((Suppliers.City) Is Null));

When I run this query, I get the following results:

Click for larger image

As you can see, the supplier columns are empty. There are only five employees that do not have suppliers in their city.

The last set I need to produce is Set 3, or the set that contains all the suppliers that do not have employees in their city. Below is the Design and Join Property view of how to construct this query:

Click for larger image

Here you can see I pick the third join criteria, "Include ALL records from 'Suppliers' and only those records from 'Employees' where the joined fields are equal." However, since I specified that I only wanted supplier records where the Employees.City column "Is Null," only suppliers that do not have employees in their city will be returned. Here is the SQL code behind the screen above:

SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees RIGHT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE Employees.City is null;

When I run this query, I get the following results:

Click for larger image

Here you can see I returned 28 Suppliers that have a null value in the Employees.City Column.

Now that I have all three query developed all I need to do is put everything together to produce my simulated FULL OUTER JOIN. To do that I just take my three different SELECT statements for set 1, 2, and 3 and put them together with a UNION ALL statement in between. In Access, I do this by using the "SQL View" of the Query Design view. Here is the SQL code for my final simulated FULL OUTER JOIN:

SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees INNER JOIN Suppliers ON Employees.City = Suppliers.City 
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees LEFT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE (((Suppliers.City) Is Null))
UNION ALL 
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees RIGHT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE Employees.City is null;

When I run this query, I get the following results:

Click for larger image

Note that this query returned a total of 37 rows. The first four rows are the records returned for the INNER JOIN I called Set 1. As you can see both the employee and supplier columns have data in them for this set. The next five rows are for Set 2, the LEFT OUTER JOIN, and therefore they do not contain any column data for the supplier table columns. And the last 28 rows are for Set 3 the RIGHT OUTER JOIN, so in these records there is no employee data.

Conclusion

Just because Access does not support the FULL OUTER JOIN clause does not mean you cannot do a FULL OUTER JOIN. It only means you have to be a little more creative to accomplish a FULL OUTER join. By thinking of the pieces it takes to build a FULL OUTER, I was able to build my own simulated FULL OUTER JOIN in Access. My method would be similar to how you could simulate the multiplication function using a series of add functions. Next time you find that Access is missing some ANSI SQL functionality try to think of the process that would be used to produce the functionality. By doing this, you may find you can build your own simulated method of accomplishing the functionality missing in Access.

» See All Articles by Columnist Gregory A. Larsen








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers