Join data sources in a query When you include multiple data sources in a query, you use joins to limit the records th...
Join data sources in a query
When you include multiple data sources in a query, you use joins to limit the records that you want to see, based on how the data sources are related to each other. You also use joins to combine records from both data sources, so that each pair of records from the sources becomes one record in the query results.
This article discusses the various types of joins and shows you how to use them in a query.
Note: Throughout this article, the term table is used to mean data source. You can also use a query in a join just as you would a table.
-
Inner Join: Show rows where a common value exists in both of the joined tables
-
Left or Right outer join: Show all rows from one table, and corresponding rows from the other table
-
Full outer join: Show all rows from both tables, and combine them if a common value exists
-
Unequal join: Combine records based on an inequality of field values
Overview
Joins are to queries what relationships are to tables: an indication of how data in two sources can be combined based on data values they have in common. Here's an illustration of a join in query Design View, with the join's properties open in a dialog box:
This line between the tables represents the join. Double-click a join to open the Join Properties dialog box (depicted) and review or change the join.
Joins are sometimes directional. This area of the dialog box shows you which table is which in the join, and which fields are used to join the tables.
This area determines the type of join: option 1 is an inner join, 2 is a left outer join, and 3 is a right outer join.
Fields from both tables can be used, and data that pertains to a given task is displayed from each. In an inner join, no other data is included. In an outer join, unrelated records from one table are also included in the query results.
Some joins are created automatically
In many cases, you don't have to think about joins - if the tables you add to a query already have a relationship, Access creates an inner join to mirror that relationship. Most of the time, this is what you want: a way to make sure that data pulled from two tables is correctly combined so that the right data from each table is included, and nothing more. But in some cases, you want a join to be more inclusive.
Types of joins
There are four basic types of joins: inner joins, outer joins, cross joins, and unequal joins. Cross joins and unequal joins are advanced join types and are rarely used, but you should know about them to have a full understanding of how joins work.
Inner joins: only related data from both tables combined
An inner join is one in which Access only includes data from a table if there is corresponding data in the related table, and vice versa. Most of the time, you will use inner joins. When you create a join and don't specify what kind of join it is, Access assumes you want an inner join. Inner joins are useful because they let you combine data from two sources based on shared values – so you only see data when there's a complete picture.
Outer joins: all the related data combined correctly, plus all the remaining records from one table
An outer join is like an inner join, but adds the remaining rows from one of the tables. Outer joins are directional: a left outer join includes all the records from the left table – the first table in the join – and a right outer join includes all the records from the right table – the second table in the join.
Full outer joins: all the data, combined where feasible
In some systems, an outer join can include all rows from both tables, with rows combined when they correspond. This is called a full outer join, and Access doesn't explicitly support them. However, you can use a cross join and criteria to achieve the same effect.
Cross joins: all the data, combined every possible way
Most of the time, a cross join is a side effect of adding two tables to a query and then forgetting to join them. Access interprets this to mean that you want to see every record from one table combined with every record from the other table – every possible combination of records. Because no data can be combined, this kind of join rarely produces useful results. But there are a few cases when a cross join is just what you need.
Unequal joins: like a regular join, but using a different comparison to combine rows
Unequal joins use an operator other than the equal sign (=) to compare values and determine whether and how to combine the data. Unequal joins aren't explicitly supported, but you can use a cross join and criteria to achieve the same effect.
Inner Join: Show rows where a common value exists in both of the joined tables
If you want to show only those rows that have matching values in the joined field, you use an inner join. Access creates inner joins automatically.
Inner joins are the most common type of join. They tell a query that rows from one of the joined tables correspond to rows in the other table, based on common values in the joined fields. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be included in the query operations.
How do I use an inner join?
To create an inner join, drag a field from one data source to a field in another data source in the query design grid. Access joins the two tables on the fields and displays a line between them.
Automatic inner joins
Most of the time, you don't need to do anything to use an inner join. If the tables that you add to a query already have relationships, Access automatically creates an inner join between each pair of related tables, when you add the tables. If referential integrity is enforced, Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol (∞) to show which table is on the "many" side.
Even if you haven't created relationships, Access automatically creates inner joins if you add two tables to a query and those tables each have a field with the same name and the same or compatible data type, if one of the fields is a primary key for its table. The "one" and "many" symbols are not displayed in this case, because referential integrity is not enforced.
If you add queries to your query, and have not created relationships between those queries, Access does not automatically create inner joins between those queries or between queries and tables. Generally, you should create them yourself (by dragging a field from one data source to a field on another data source). If you don't, you may end up with a cross join – confusing results if that's not what you want.
SQL syntax for an inner join
Inner joins are specified in SQL in the FROM clause, as shown below:
FROM table1 INNER JOIN table2 ON table1.field1compopr table2.field2
The INNER JOIN operation has these parts:
| Part | Description |
| table1, table2 | The names of the tables from which records are combined. |
| field1, field2 | The names of the fields that are joined. If they are not numeric, the fields must be of the same data type and contain the same kind of data, but they do not have to have the same name. |
| compopr | Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>." |
For more information about inner join syntax, see the topic INNER JOIN Operation.
Left or Right outer join: Show all rows from one table, and corresponding rows from the other table
Outer joins tell a query that although some of the rows on both sides of the join correspond exactly, the query should include all of the rows from one table, and also those rows from the other table that share a common value on both sides of the join (combining the data when both tables include a row).
Outer joins can be left outer joins or can be right outer joins. In a left outer join, the query includes all of the rows from the first table in the SQL statement FROM clause, and only those rows from the other table where the joining field contains values common to both tables. In a right outer join, the query includes all of the rows from the second table in the SQL statement FROM clause, and only those rows from the other table where the joining field contains values common to both tables.
Note: You can easily tell which table is the left table or the right table in a given join by double-clicking the join and then looking in the Join Properties dialog box. Or, in SQL View, you can examine the FROM clause for join details.
Because some of the rows on one side of an outer join will not have corresponding rows from the other table, some of the fields returned in the query results from that other table will be empty when the rows do not correspond.
How do I use an outer join?
You create an outer join by modifying an existing inner join. If no inner join exists, you create one, and then change it to an outer join.
Change an inner join to an outer join
-
In query Design view, double-click the join you want to change.
The Join Properties dialog box appears.
-
In the Join Properties dialog box, note the choices listed beside option 2 and option 3.
-
Click the option that you want to use, and then click OK.
-
Access displays the join and shows an arrow that points from the data source where all rows will be included to the data source where only those rows that satisfy the join condition will be included.
Ambiguous outer joins
If you create a query that contains a LEFT JOIN and an INNER JOIN, Access may not be able to determine which join operation to perform first. Because the results are different depending on whether the left join or the inner join is performed first, Access displays an error message about ambiguous outer joins.
To correct this error, change the query so that it is clear which join to perform first.
Outer joins and query updateability
In an Access app, a field in a query of multiple tables is updateable only if the following conditions are true:
-
The field is from the 'most-many' table of the query – the table that looks up data in another table.
-
The query output includes the primary key from the most-many table.
-
If the most-many table is part of an outer join, it is on the inner side of the join: the left table of a left outer join or the right table of a right outer join.
If any of the conditions is not true, the field will be read-only, whether in the query datasheet or in a view that is based on the query.
SQL syntax for an outer join
Outer joins are specified in SQL in the FROM clause, as shown below:
FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1compopr table2.field2
The LEFT JOIN and RIGHT JOIN operations have these parts:
| Part | Description |
| table1, table2 | The names of the tables from which records are combined. |
| field1, field2 | The names of the fields that are joined. The fields must be of the same data type and contain the same kind of data, but they do not need to have the same name. |
| compopr | Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>." |
For more information about outer join syntax, see the topic LEFT JOIN, RIGHT JOIN Operations.
Full outer join: Show all rows from both tables, and combine them if a common value exists
When you want to show all rows from two tables and join them based on common values, use a full outer join. Access doesn't explicitly support full outer joins, but you can achieve the same effect by using a cross join and criteria.
-
Create a query and add the two tables. If Access creates any joins, delete them.
-
Add any fields that you want for the query output.
-
Add the join fields from both tables, and clear the check box in the Show row for them.
-
In the query design grid, under the first table's join field, enter the second table's join field in the Criteria row. If the join fields have the same field name, be sure to include the table name when you refer to them.
-
In the Or row, under the criterion you just entered, type Is Null.
-
In the next Or row (just below the first one), under the second table's join field, type Is Null.
The query returns the combined set of:
-
Rows where the joined field values match, data combined accordingly (in effect, an inner join)
-
Rows where there is no value for the joined field in the first table (in effect, the outside of a left outer join)
-
Rows where there is no value for the joined field in the second table (in effect, the outside of a right outer join)
Cross join: all possible combinations of rows
Any time you include tables in a query but don't join them, a cross join occurs. Access combines each row from one table with each row from another table, resulting in what is called a cross product or a Cartesian product. Usually, this is not what you want, but cross joins can be useful for exploring hypotheticals.
Why would I use a cross join?
If you want to examine every possible combination of rows between two tables or queries, use a cross join. Suppose your business had a spectacular year, and you are considering giving a rebate to your customers. You can build a query that sums each customer's purchases, create a small table that has several possible rebate percentages, and combine the two in another query that performs a cross join. The cross product displays a set of hypothetical rebates for each customer – just what you want.
How do I use a cross join?
Add two tables to a query, don't create a join between them, and include fields from both in your query output. Access combines every row from each table or query that is not explicitly joined to any other table or query to every other row in the query results. Consider the rebate example from the preceding paragraph. Assume you have 91 customers, and that you want to look at five possible rebate percentages. The cross join produces 455 rows (the product of 91 and 5).
If the tables you add are related, Access creates an inner join automatically. You can delete this automatic join if necessary to produce a cross join.
As you might imagine, unintentional cross joins can create huge numbers of rows in your query results. Moreover, these results are generally meaningless, because the rows aren't combined on the basis of shared data and the combinations don't correspond to anything.
Unequal join: Combine records based on an inequality of field values
Joins do not have to be based on the equivalence of the joined fields. A join can be based on any comparison operator, such as greater than (>), less than (<), or does not equal (<>). Joins that are not based on equivalence are called unequal joins.
If you want to combine the rows of two sources of data based on field values that are not equal, you use an unequal join. Typically, unequal joins are based on either the greater than (>), less than (<), greater than or equal to (>=), or less than or equal to (<=) comparison operators. Unequal joins that are based on the does not equal (<>) operator can return almost as many rows as cross joins, and the results can be difficult to interpret.
How do I use an unequal join?
Access doesn't explicitly support unequal joins in Design View, but you can achieve the same effect with a cross join and criteria:
-
Create a query and add the two tables for the unequal join. If Access creates any joins automatically, delete them.
-
Add the output fields you want.
-
Add the join field from the first table, and clear the check box in the Show row.
-
In the Criteria row, type the inequality operator you want to use, followed by the second table's join field.
The query returns rows combining data from the two tables based on the inequality you specified.
Delete a join
If Access automatically creates a join that you do not want, or if you create a join by mistake — for example, a join between two fields that have dissimilar data types — you can delete the join.
-
In the query design grid, click the join you want to remove.
-
Press DELETE.
-or-
-
In the query design grid, right-click the join you want to remove, and then click Delete.
COMMENTS