Simulate a full outer join in Access SQL

Access gives us a partial implementation of the Structured Query Language (SQL) but it doesn't include the ability to return a full outer join between two tables. There are four main types of query in SQL, (described here) but Access only allows us to create an inner join or a left or right outer join. If we need a full outer join then we have to do a little bit more work to simulate it.

The way to approach the problem is to think about the types of row that you'll get in a full outer query. Using the typical Customers and Orders database as an example there are three types of rows in a full outer join:

  • Customers with orders.
  • Customers without orders.
  • Orders without customers.

If we could write a query for each of these sets of records then we could get the full outer query as the union of the three simple queries.

The first set is easy, it's just an inner join.

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
   FROM Customers INNER JOIN Orders
   ON Customers.CustomerID = Orders.CustomerID;

The second set is something like a left outer join but a standard left outer join will return customers with orders as well as those without orders. We only need those rows that do not have a match in the Orders table and we can pick them up by looking for a null in the OrderID field. Using Microsoft Access syntax this would be:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
   FROM Customers LEFT JOIN Orders
   ON Customers.CustomerID = Orders.CustomerID
   WHERE IsNull(Orders.OrderID);

The final set is similar but we need a right outer join and we only want to see the orders that do not match a customer:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.OrderDate
   FROM Customers RIGHT JOIN Orders
   ON Customers.CustomerID = Orders.CustomerID
   WHERE IsNull(Customers.CustomerID);

The final step is to use a union to get the records from all three queries. You could do the whole operation in a single query but I prefer to write the inner, left and right joins separately so that I can test them individually.

SELECT * FROM innerjoin
UNION ALL
SELECT * FROM leftjoin
UNION ALL
SELECT * FROM rightjoin;

MS Access technical tips

Visual FoxPro technical tips

General Tips

 

More tips from Alvechurch Data

Alvechurch Data - Microsoft FoxPro and Access Database Development and Training

Alvechurch Data are based close to Birmingham and provide Microsoft Access and Visual FoxPro training, development and support.

Read More

Custom Toolbars

Your Access database will look more impressive if you add custom toolbars.

Read More

Development Services

Alvechurch Data specialise in Microsoft Access and Visual FoxPro databases and have fifteen years experience developing databases for small business use.

Read More

Access and Visual FoxPro Training

Microsoft Access and Visual FoxPro training courses and workshops offered by Alvechurch Data in Birmingham.

Read More

Support Services

How Alvechurch Data can help you with maintenance and support of your Access and FoxPro databases.

Read More