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;