Microsoft Access Outer Join Query: Finding All Records in One Table but Not Another and Creating "Not In" Queries

by Molly Pell, Quality Assurance Specialist

When querying data from multiple tables in Microsoft Access or SQL Server, we usually use Inner Joins to link records with values that exist in both tables. But what if we need to find records that exist in one table but not the other?

It turns out we can do this without any programming since it's part of the query SQL syntax. Simply use an Outer Join to generate "Not In" results. Use the LEFT JOIN or the RIGHT JOIN syntax depending on which table is referenced first in the query:

• LEFT JOIN returns all records from the first table, even if there are no matching records in the second table.
• RIGHT JOIN returns all records from the second table, even if there are no matching records in the first table.

These Joins can also be used to only retrieve the record values that exist in one table and not the other.

There are several scenarios where Outer Joins should be used:

• Showing All Records from One Table with Fields from a Second Table If Linked Records Exist
• Finding Records in One table that Don't Exist in Another Table (the Not-In Query)
• Finding Records that Exist in One Table, but Not in a Query (subset of another table)
• Troubleshooting Missing Data (Orphaned Rows)
• Finding Differences Between Two Tables

The following examples use the Customers and Orders tables from the Northwind 2007.accdb sample database to demonstrate these scenarios.

Showing All Records from One Table with Fields from a Second Table If Linked Records Exist

Assume that we want a list of all customers, and if they have any Orders, we want to show the order dates. In this scenario, we want to see customers with orders AND customers without orders. We can use a Left Outer Join to accomplish this.

Designing the Query

Follow these steps to design the query interactively (or paste the Query SQL instead):

1. In the Designer Window, add the Customers and the Orders tables. The Join line is automatically added to join [Customers].[ID] to [Orders].[Customer ID].
2. Double click on the line to open the Join properties
   
   
   Notice that this shows the name of the Left table and the Right table, and lets you choose your Join option. In this case, we want option #2: “Include ALL records from ‘Customers’ and only those records from ‘Orders’ where the joined fields are equal.”

3. Click OK to return to the Designer Window, and add the following fields:

The designer window should look like this:

Query SQL

The SQL statement for the query is as follows:

SELECT Customers.Company, Orders.[Order Date]
FROM Customers LEFT JOIN Orders ON Customers.ID = Orders.[Customer ID]

Query Results

Run the query, and you see all companies and their orders, if any. If the company has no associated order, it still shows in the list, only with a Null Order Date.

If we used an Inner Join, then Company B would be excluded from the results, since there is no matching record in the Orders table.

Finding Records in One table that Don't Exist in Another Table (the Not-In Query)

Now assume that we ONLY want to find the customers who have not placed any orders. Just like in the first scenario, we will use a Left Outer Join to accomplish this. This time, however, we’ll add criteria to exclude the rows that have orders.

Designing the Query

In the Designer Window, add the Customers and the Orders table, and set the Join type to be a Left Outer Join (as explained in Steps 1 and 2 above).

In the Designer Window, and add the following fields:

The designer window should look like this:

Query SQL

The SQL statement for the query is as follows:

SELECT Customers.CompanyFROM Customers
LEFT JOIN Orders ON Customers.ID = Orders.[Customer ID]
WHERE (((Orders.[Order ID]) Is Null))

Query Results

Run the query, and you see only the companies who have no associated orders:

Finding Records that Exist in One Table, but Not in a Query (subset of another table)

Now assume now that we want to find customers who have not placed orders in the last year. The steps are similar to finding records that exist in one table, but not another, except that rather than joining the Customers table to the Orders table, we join it to a saved query that returns Orders within the last year.

First save the following query as "qryOrdersLastYear":

SELECT * FROM Orders WHERE ((([Order Date])>=DateAdd('yyyy',-1,Date())))

And follow the steps above, only using qryOrdersLastYear instead of Orders. The SQL statement for the query is as follows:

SELECT Customers.CompanyFROM Customers
LEFT JOIN qryOrdersLastYear ON Customers.ID = qryOrdersLastYear.[Customer ID]
WHERE (((qryOrdersLastYear.[Order ID]) Is Null))

Troubleshooting Missing Data (Orphaned Rows)

Outer joins also come in handy when we want to troubleshoot missing data. For instance, if we find ourselves with orphaned Order rows, we can find which orders don’t have an associated customer.

Designing the Query

Follow these steps to design the query interactively (or alternatively, you can paste the Query SQL below)

1. In the Designer Window, add the Customers and the Orders table. The Join line is automatically added to join [Customers].[ID] to [Orders].[Customer ID].
2. Double click on this line to open the Join properties:
   

Notice that this shows you the name of the Left table and the Right table, and lets you choose your Join option. In this case, we want option #3: “Include ALL records from ‘Orders’ and only those records from ‘Customers’ where the joined fields are equal.” This is a Right Outer Join, since the Orders table is our Right table.

Click OK to return to the Designer Window, and add the following fields:

The designer window should look like this:

Query SQL for Missing or Orphaned Records

The SQL statement for the query is as follows:

SELECT Orders.[Order ID]
FROM Customers
RIGHT JOIN Orders ON Customers.ID = Orders.[Customer ID]
WHERE (((Customers.ID) Is Null))

Query Results

Run the query, and you see any orphaned Orders records (orders that have a non-existent customer). For instance, if Customer 1 is missing from the Customers table, the results are:

(Note that Referential Integrity was removed from the relationship for the purpose of this example. Enforce Referential Integrity to prevent orphaned records. See http://support.microsoft.com/kb/304466 for details.)

Finding Differences Between Two Tables

We can also use Outer Joins to find rows that are missing between tables. Assume that we have two copies of the Customers table, but records were added and deleted from both tables. We need to see see the records from both tables that aren't in the other.

To see rows in Customers that are not in Customers1, the Query Designer looks like:

and the SQL is:

SELECT Customers.ID, Customers.Company
FROM Customers
LEFT JOIN Customers1 ON Customers.ID = Customers1.ID
WHERE (((Customers1.ID) Is Null))

To see the opposite (rows in Customer1 that are not in Customers), the designer looks like:

and the SQL is

SELECT Customers1.ID, Customers1.Company
FROM Customers
RIGHT JOIN Customers1 ON Customers.ID = Customers1.ID
WHERE (((Customers.ID) Is Null))

Additional Resources

• Total Access Detective finds differences between Microsoft Access databases and objects including data between tables
• Microsoft KnowledgeBase Article: Defining relationships between tables in a Microsoft Access database