Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท Queries

Joins in Access Queries: Inner, Outer, Unmatched and Self

Combine tables in Access queries with inner, left and right joins, find unmatched records, join on several fields, build self joins and avoid accidental cross joins.

Most useful questions need more than one table. Which customers placed orders in March? Which products have never been sold? Who reports to whom? A join combines rows from two tables by matching values in related fields, usually a primary key and a foreign key.

Access draws a join line between tables in Query Design view. Double-click the line to choose the join type. This lesson explains each type, how to find unmatched records with the Find Unmatched Query Wizard or by hand, and the traps that give wrong totals.

The data

All examples use the same compact shop data. The customer Casa Luz has no orders, and the product Standing desk has never been ordered, which is exactly what makes the outer joins interesting.

SQL The sample shop

Press Run (or Ctrl + Enter) to see the result here.

Inner join: only matching rows

An inner join returns a row only when both tables have a match. It is the default when you add two related tables to a query. Access describes it as "Only include rows where the joined fields from both tables are equal" (option 1 in the Join Properties dialog).

In Access SQL, an inner join is written INNER JOIN ... ON ...:

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

Customers with no orders (Casa Luz) do not appear, and neither do orders with no customer.

SQL Inner join

Press Run (or Ctrl + Enter) to see the result here.

Which rows survive?

Inner join Only rows that match both sides
Left join All left rows, matches from the right
Right join All right rows, matches from the left

Nulls fill the side with no match.

1 / 2

Left and right outer joins

An outer join keeps all rows from one table even when there is no match in the other. Where no match exists, the missing side shows Null.

In the Join Properties dialog (double-click the join line) the options are:

  1. Include only rows where the joined fields from both tables are equal. (inner join)
  2. Include ALL records from Customers and only those records from Orders where the joined fields are equal. (left outer join)
  3. Include ALL records from Orders and only those records from Customers where the joined fields are equal. (right outer join)

The list names the tables for you, so read it carefully. In SQL: LEFT JOIN keeps every row from the table written before it, RIGHT JOIN from the table after it. Access has no FULL OUTER JOIN. To get one, combine a left and a right join with UNION.

SQL Left join: all customers, even without orders

Press Run (or Ctrl + Enter) to see the result here.

Casa Luz now appears with a Null OrderID.

Finding unmatched records

"Which customers have never ordered?" is one of the most common questions in database work. You have two options.

The wizard. Choose Create > Query Wizard > Find Unmatched Query Wizard, pick the table whose rows you want (Customers), pick the related table (Orders), choose the matching field and finish. Access builds a left join with a Null filter.

By hand. Create a left join and put Is Null as the criterion on the related table's key:

SQL
SELECT Customers.CustomerName
FROM Customers LEFT JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID Is Null;

The filter keeps only the customers for whom the join found nothing. Clear Show on the Null-tested field, since it is always blank.

SQL Unmatched customers and unsold products

Press Run (or Ctrl + Enter) to see the result here.

Unmatched queries also detect orphan records, the problem that can stop you enforcing referential integrity: put the child table first and test the parent's key for Null.

Joining more than two tables

Add every table you need and Access draws a line for each relationship. For three or more tables, Access SQL requires nested parentheses around the joins, and the designer adds them for you:

SQL
SELECT Customers.CustomerName, Products.ProductName, OrderDetails.Quantity
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
  INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID)
  INNER JOIN Products ON Products.ProductID = OrderDetails.ProductID;

Standard SQL needs no extra parentheses:

SQL Four tables joined

Press Run (or Ctrl + Enter) to see the result here.

Joining on more than one field

Sometimes two fields together identify a match, such as a region and a year. Drag a second join line between the second pair of fields. In SQL, join the conditions with AND:

SQL
FROM Budgets INNER JOIN Actuals
  ON Budgets.Dept = Actuals.Dept AND Budgets.Year = Actuals.Year

Non-equi joins

Joins normally compare with =, but Access also permits <, >, <=, >= and <> in the join properties. These are rare, but handy for banding, such as joining a score to the grade range it falls in. In Access they cannot be shown in the design grid for every combination and are best written in SQL view.

Self joins

A self join joins a table to itself. The classic example is an Employees table with a ManagerID that points to another employee. Add the table twice in Query Design view; Access names the second copy Employees_1. Drag ManagerID of the first onto EmployeeID of the second.

SQL Employees and their managers

Press Run (or Ctrl + Enter) to see the result here.

Cross joins by accident

If two tables are in a query with no join line, Access pairs every row of one with every row of the other: 1,000 customers and 5,000 products would give five million rows. This Cartesian product is almost never what you want. If a query takes forever or returns ridiculous counts, look for a missing join line.

Joins and wrong totals

When you join a parent to a child and then sum a field from the parent, each parent row repeats once per child. A customer's CreditLimit joined to three orders appears three times, so summing it triples the total. Aggregate each table separately, or sum fields from the child side only. The totals lesson shows how.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join returns all customers, including those with no orders?

    A left join keeps every row of the left table (Customers) and fills Null where Orders has no match.
  2. 2. How do you find customers who have never placed an order?

    The Null on the Orders side shows the join found nothing; that is what the Find Unmatched Query Wizard builds.
  3. 3. Access SQL has no keyword for which join type?

    Access has no FULL OUTER JOIN. Combine a left and a right join with UNION.
  4. 4. What causes a query to return millions of rows when two small tables are added?

    Without a join, every row of one table is paired with every row of the other.

Summary

  • Inner joins return matches only; left and right outer joins keep all rows from one side and fill Null for missing matches.
  • Unmatched queries (a left join plus Is Null) find childless parents and orphan records.
  • Access needs nested parentheses for three or more joined tables and has no full outer join.
  • Self joins use a table twice, with an alias, for hierarchies such as managers.
  • A missing join line creates a cross product, and joining parents to children can inflate parent-side totals.

Next: Totals Queries and Calculated Fields