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
|