Creating an outer join query in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 97, 2000, and 2002

In Access, there are two types of joins based on equality of matching fields (or columns) — inner joins and outer joins. An inner join, which is the default join type, combines rows from two tables or queries only if values in the joined fields match. The matching fields must have compatible data types or contain similar data. If one table or query doesn't have a matching field in the other table or query, neither row appears in the query's results. An outer join, on the other hand, includes rows even if they do not have matching fields in the joined table.

ShowExample: outer join of Customers and Orders in the Northwind sample Access database

Query results from an outer join

1 An arrow points to the table contributing only the matching records.
2 The query displays empty cells where there is no matching record from another table.
3 Outer join.

ShowThree types of outer joins

You can create three types of outer joins to specify which unmatched rows to include in the query results: left outer join, right outer join, or full outer join.

Left outer join     All rows from the leftmost table or query in the JOIN clause are included. Unmatched rows in the rightmost table or query in the JOIN clause do not appear.

ShowAccess database SQL example


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

ShowAccess project SQL example

SELECT dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.Customers LEFT OUTER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

Right outer join     All rows in the rightmost table or query in the JOIN clause are included. Unmatched rows in the leftmost table or query in the JOIN clause are not included.

ShowAccess database SQL example

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

ShowAccess project SQL example


SELECT dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.Customers RIGHT OUTER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

Full outer join     All rows in all joined tables and queries are included, whether they match or not.

ShowAccess project SQL example


SELECT dbo.Customers.CompanyName, dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.Customers FULL OUTER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID

 Note   You cannot define a full outer join in an Access database.

ShowCreate an outer join in an Access database

  1. In query Design view, drag a field from the field list for one table or query to the equivalent field in the field list for the other table or query.

Drag the field to join the tables

  1. Double-click the join line between the field lists for the tables or queries.
  2. In the Join Properties dialog box, click the join option you want, and then click OK.
  3. Do one of the following:
    • To define a left outer join, click option 2.
    • To define a right outer join, click option 3.

ShowCreate an outer join in an Access project

  1. In the Diagram pane of the Query Designer, drag the name of the join column in the first table or query and drop it onto the matching column in the second table or query.
  2. In the Diagram pane, right-click the join line, and then click Properties.
  3. On the Join Line tab, under Include rows, do one of the following:
    • To create a left outer join, select the first check box — for example, All rows from Customers.
    • To create a right outer join, select the second check box — for example, All rows from Orders.
    • To create a full outer join, select both check boxes — for example, All rows from Customers and All rows from Orders.