Click any image to view a larger version



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

QueryJoins1-2Tables

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

QueryJoins2-InnerJoinProperties

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

QueryJoins3-Inner

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:

QueryJoins4-Left

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:

QueryJoins5-Right

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:

QueryJoins6-Union


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

QueryJoins7-Cartesian

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:

QueryJoins8-3Tables



f)   Multiple Inner Joins                                                                                                                                   Return To Top

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

QueryJoins9-Inner3Tables

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

QueryJoins10-Inner3Tables



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

QueryJoins11-Outer3Tables

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;



QueryJoins12-Outer3Tables

Another valid possibility is Table3=> Table2=>Table1

QueryJoins13-Outer3Tables

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;



QueryJoins14-Outer3Tables

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

QueryJoins15-Outer3Tables

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;



QueryJoins16-Outer3Tables



h)   Ambiguous Joins                                                                                                                                       Return To Top

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

QueryJoins17-Ambiguous1

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:

QueryJoins18-AmbiguousError

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

QueryJoins19-AmbiguousFix1

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:

QueryJoins20-AmbiguousFix2

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

QueryJoins21-AmbiguousFix3

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:

QueryJoins22-AmbiguousFix4

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

QueryJoins23-MixedJoins

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;



QueryJoins24-MixedJoins2

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