Last Updated 14 Mar 2019                                                Difficulty level :   Easy

Section Links:
          Inner Join
          Left Outer Join
          Right Outer Join
          Union Query
          Cartesian Join
          Inner Joins (Multiple tables)
          Outer Joins (Multiple tables)
          Ambiguous Joins
          Mixed Inner/Outer Joins
          Downloads


When you link two or more tables together in a query, the output will depend on the type of join used between the tables.

There are 5 main options to choose from
a)   Inner Join
b)   Left Outer Join
c)   Right Outer Join
d)   Union Query (AKA Full Outer Join)
e)   Cartesian Join (AKA No Join)

To illustrate the effect of each, the first part of this article uses two randomly chosen tables with 4 and 8 records respectively



Both tables have an ID fields which will be used as the link fields in these queries



a)   Inner Join                                                                                                                                                 Return To Top

The two tables are linked by connecting the two ID fields. By default an inner join is created.

Right click on the joining line and select Join Properties.
The Join Properties dialog box appears and explains what the join means



When the query is run, it has 3 records as only ID 2, 3 and 4 are in both tables



The query SQL is:

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

The Join Properties dialog box also explains the next two options



b)   Left Outer Join                                                                                                                                         Return To Top

In this case, join option 2 is chosen – all records from table 1 (on the left) and only matching records in table 2

Notice the direction of the arrow in the query design.
All 4 records from table 1 are listed with the corresponding data from table 2
The query design and results are:



The query SQL is:

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID;

c)   Right Outer Join                                                                                                                                         Return To Top

In this option 3 is chosen – all records from table 2 (on the right) and only matching records in table 1

Notice the direction of the arrow in the query design. In this case there are 8 records in the query output
The query design and results are:



The query SQL is:

SELECT Table2.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField
FROM Table1 RIGHT JOIN Table2 ON Table1.ID=Table2.ID;

d)   Union Query                                                                                                                                               Return To Top

Access does not support the full outer join needed to get all values from both tables

Instead a union query is used to achieve this outcome.
It is effectively two (or more) queries ‘unioned’ together. Union queries can only be created in SQL view

NOTE:
Each part of a union query must have the same number of fields with the same datatypes.
That isn't an issue in this example. However, you should add 'DUMMY' fields to one or both parts to achieve that where necessary.

The query SQL is:

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID;
UNION SELECT Table2.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField
FROM Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID;

The query results show all records from both tables with gaps where there is no corresponding record:




e)   Cartesian Join                                                                                                                                           Return To Top

In this case the join is removed so we have two unconnected tables.
The query output has a separate record for every combination of records from each table
In this case, this gives a total of 4x8 = 32 records



The query SQL is:

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.ID, Table2.TextField
FROM Table1, Table2
ORDER BY Table1.ID, Table2.ID;

Using a Cartesian join on 2 large tables will give a huge number of records.
For example, a Cartesian join on 2 tables of 10,000 and 20,000 records woulds give an output of 200,000,000 records!

For most purposes, a Cartesian join is undesirable though it does have its uses.
As an example, it could be used to populate a calendar events table with all the dates for several calendar years.



Queries using 3 (or more) tables

Queries can also be made using one or more additional tables. For the remainder of this article, the following tables are used:





f)   Multiple Inner Joins                                                                                                                                   Return To Top

This will only show all records that are in EACH of the tables



The query SQL is:

SELECT Table2.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
FROM (Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID) INNER JOIN Table3 ON Table1.ID = Table3.ID;

As all conditions need to be met, this often REDUCES the number of records shown





g)   Multiple Outer Joins                                                                                                                                 Return To Top

There are various possibilities. In each case the arrow ‘flow direction’ needs to be consistent

For example: Table1=> Table2=>Table3



The query SQL is:

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
FROM (Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID) LEFT JOIN Table3 ON Table2.ID = Table3.ID;

Another valid possibility is Table3=> Table2=>Table1



The query SQL is:

SELECT Table3.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
FROM Table3 LEFT JOIN (Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID) ON Table3.ID = Table2.ID;

Or the 'flow' can start from Table2 which has the most records:



The query SQL is:

SELECT Table2.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
FROM (Table1 RIGHT JOIN Table2 ON Table1.ID = Table2.ID) LEFT JOIN Table3 ON Table2.ID = Table3.ID;

h)   Ambiguous Joins                                                                                                                                       Return To Top

Not all joins can be processed. In this case, the flow direction isn’t consistent:



The query SQL is:

 SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
 FROM Table3 LEFT JOIN (Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID) ON Table3.ID = Table2.ID;

The query fails because it contains ambiguous outer joins that cannot be processed. This error message is displayed:



To fix this issue, do the query in two stages so one of the joins is processed first.

For example, use the existing qryLeftJoin and link to Table3



The query SQL is:

SELECT Table3.ID, qryLeftJoin.DummyTest, qryLeftJoin.NumberText, qryLeftJoin.TextField, Table3.TextField2
FROM qryLeftJoin RIGHT JOIN Table3 ON qryLeftJoin.ID = Table3.ID;

The query has 3 records:



Alternatively, create a right outer join query based on Tables 2 & 3 then link to Table1 using a left join



The query SQL is:

SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, qryRightJoin32.TextField, qryRightJoin32.TextField2
FROM qryRightJoin32 RIGHT JOIN Table1 ON qryRightJoin32.ID = Table1.ID;

This time, the query has 4 records:



The fact that the outcome is different explains why the original ambiguous joins query could not be processed.
Access has no means of knowing how to resolve what the query actually means.



i)   Mixed Inner/Outer Joins                                                                                                                             Return To Top

It is also possible to use a mixture of inner and outer joins.
For example, this get all records in both table 1 & 2 together with any matching records in table 3



The query SQL is:

SELECT Table2.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField, Table3.TextField2
FROM (Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID) LEFT JOIN Table3 ON Table2.ID = Table3.ID;

NOTE:
Using more tables increases the chances that the query will be READ ONLY and/or show duplicate records

All the above methods can be adapted to manage cases where duplicate records are displayed.

There are 2 options :
i)   Unique Values = Yes (SELECT DISTINCT) – this automatically makes the query READ ONLY
ii)   Unique Records = Yes  (SELECT DISTINCTROW)

These will be discussed in more detail in a future article

In addition, other query join types such as self-joins and non-equi joins will be covered in another article



Downloads                                                                                                                                                     Return To Top

Click to download

The sample database:                       QueryJoins                                        Approx 0.6 MB (zipped)
A PDF version of this article:              Query Join Types


I would be grateful for any feedback on this article including details of any errors or omissions



Colin Riddington           Mendip Data Systems                 Last Updated 14 Mar 2019

Return to Access Articles Page

Return to Top