Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Query Join Types

Return to Home Page

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, this article uses two randomly chosen tables with 4 and 8 records respectively

 

 

Colin Riddington      Mendip Data Systems        17/01/2019

Return to Top
* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Return to Access Articles

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

Difficulty level :   Easy

 

 SELECT DISTINCT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField

 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

QueryJoins1-2Tables QueryJoins2 - Inner-JoinProperties

Click any image to view a larger version . . .

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

a)  Inner Join

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

QueryJoins3 - Inner QueryJoins4 - Left QueryJoins5 - Right

The query SQL is:

The Join Properties dialog box also explains the next two options

b)  Left Outer Join

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:

 

 SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.TextField

 FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID;

The query SQL is:

c)   Right Outer Join

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:

 

 SELECT [Table2].ID, [Table1].DummyTest, [Table1].NumberText, [Table2].TextField

 FROM Table1 RIGHT JOIN Table2 ON Table1.ID=[Table2].ID;

The query SQL is:

d)   Union Query

Access does not support a 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 QueryJoins7 - Cartesian QueryJoins8-3Tables

e)   Cartesian Join

In this case the join is deleted so we have two unconnected tables.

The query output has a separate record for every combination of records from each table

This gives a total of 4x8 = 32 records

 

 SELECT Table1.ID, Table1.DummyTest, Table1.NumberText, Table2.ID, Table2.TextField

 FROM Table1, Table2

 ORDER BY Table1.ID, Table2.ID;

The query SQL is:

For most purposes, a Cartesian join is undesirable though it does have its uses.

For example, it could be used to populate a calendar events table with all the dates for a calendar year

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)   Inner Joins

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

 

 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;

The query SQL is:

QueryJoins10-Inner3Tables QueryJoins9-Inner3Tables

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

g)   Outer Joins

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

 

For example: Table1=> Table2=>Table3

 

 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;

The query SQL is:

QueryJoins11-Outer3Tables QueryJoins12-Outer3Tables QueryJoins13-Outer3Tables

 

 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;

Another valid possibility is Table3=> Table2=>Table1

QueryJoins14-Outer3Tables QueryJoins15-Outer3Tables QueryJoins16-Outer3Tables

 

 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;

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

h)   Ambiguous Joins

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

QueryJoins17-Ambiguous1 QueryJoins18-AmbiguousError QueryJoins19-AmbiguousFix1 QueryJoins20-AmbiguousFix2

 

 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

 

 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

 

 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:

QueryJoins21-AmbiguousFix3 QueryJoins22-AmbiguousFix4 QueryJoins23-MixedJoins QueryJoins24-MixedJoins2

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 means.

i)   Mixed Inner/Outer Joins

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

 

 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