← Functions
joinsunionaccesssqlqueries

Joins and Union in Microsoft Access and SQL

How to use inner joins, outer joins, and union queries in Microsoft Access to combine and retrieve data from multiple tables.

When working with Microsoft Access or SQL queries, understanding join properties is essential. There are three types: inner join, left outer join, and right outer join. Access creates queries with inner joins by default.

Inner Join

An inner join returns only records that have matching data in both tables. If you have 5 salespersons but only 3 made sales in December, an inner join query will only show those 3 — the other 2 won't appear because they have no entries in the SalespersonPerformance table.

Left Outer Join

If you want all records from the left table to appear regardless of whether there's a match, use a left outer join. To change the join type in Access:

  1. Open the query in Design View
  2. Right-click the connecting line between the two tables and choose Join Properties
  3. Change the selection from 1 to 2 and click OK

The connection line will change to an arrow indicating a left outer join. In SQL view you'll see LEFT JOIN instead of INNER JOIN.

To show zero instead of blank for unmatched records, replace the field in the query with:

Sales: IIf(IsNull([DecemberSale]),0,[DecemberSale])

Right Outer Join

Right outer join follows the same logic as left outer join — the only difference is that all records from the right table are returned instead of the left.

UNION Query

A union query combines the data of two tables into a single result set. Requirements:

  • Field names must be exactly the same in both tables
  • Data types of matching fields must be identical
  • Fields that exist in only one table cannot be included

Creating a Union Query

Union queries cannot be created with the query wizard — you must write SQL directly. Go to the Design tab and click the Union icon to open a blank SQL editor.

Basic syntax:

SELECT fieldname1, fieldname2 FROM table1
UNION
SELECT fieldname1, fieldname2 FROM table2;

If both tables have the same number of fields:

SELECT * FROM table1
UNION
SELECT * FROM table2;

Click the View button to see the results. After saving, the union query will appear in the navigation pane.

Union queries are especially useful when working with tables created by "make-table" queries, where you need to combine interim results before further processing.