An Access INNER JOIN returns only matching rows from both sources. A LEFT JOIN keeps every row from the left source and fills right-side columns with Null when no match exists. The correct choice depends on whether unmatched left rows belong in the result.
Last updated: September 26, 2026.
SELECT c.CustomerID,
c.CustomerName,
o.OrderID,
o.OrderedAt
FROM Customers AS c
LEFT JOIN Orders AS o
ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerName, o.OrderedAt;This query includes customers who have no orders. Replacing LEFT JOIN with INNER JOIN removes those customers from the result.
Put right-table filters in the correct place
A common mistake is adding WHERE o.OrderedAt >= Date()-30 to a LEFT JOIN. Rows without orders have Null in o.OrderedAt, so the WHERE clause removes them and the result behaves like an INNER JOIN. When appropriate, filter the right source in a saved query first and LEFT JOIN that query.
Understand duplicate-looking rows
A one-to-many join returns one output row for every matching child. A customer with five orders appears five times; DISTINCT may hide the symptom without answering the business question. Aggregate the orders, select a specific child record, or display the rows in a subreport. Use the top-record-per-group pattern when one related row is required.
Microsoft’s join guide describes INNER, outer, cross, and unequal joins and shows how Access represents them in Design View.
Choose the join deliberately
- Use INNER JOIN when a valid match is required.
- Use LEFT JOIN when every left-side record must remain visible.
- Use LEFT JOIN plus Is Null to find unmatched left rows.
- Check that joined fields have compatible data types and useful indexes.
An Access update query with a join normally uses INNER JOIN because only matched destination rows should change. Preview the same relationship as a SELECT before running an action query.
