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;
|
