You can retrieve data from two or more tables in the same query by joining the tables. Joined tables have a relationship to one another based on data they have in common.
For example, in a publishers application, the publishers and the titles tables might each contain a publisher ID column. If so, you can create a query that joins the two tables and produces results that include the publisher name for each book, as shown in the following illustration:
Joining tables creates a result set consisting of a virtual table with rows that combine information from all joined tables. You can specify which columns appear in the result set by selecting particular columns from each of the joined tables. You can also include sort specifications and search conditions, just as you do for queries involving single tables.
To join tables, you specify a join condition. You can create join conditions based on any comparison operator. The most common join condition is to match values exactly — that is, the query matches a value in a column of the first table against values in a column of the other table exactly. (This is sometimes referred to as an "equijoin.") For example, in the earlier illustration, the join condition is that the pub_id column in both tables are equivalent.
You can join any number of tables. If you join more than two tables, two tables are joined, the resulting virtual joined table is joined with the next tables, and so on, until a single virtual joined table is obtained.
Typically, you join tables that are in the same database. However, depending on what database server you are using, you might be able to join tables from different databases.
| To | See |
| Learn the different ways in which tables can be joined | Types of Joins |
| Learn how the Query Designer displays join information in the Diagram pane | How the Query Designer Represents Joins |
| Learn what rules the Query Designer uses to determine if tables should be joined | Joining Tables Automatically |
| Join tables yourself | Joining Tables Manually |
| Specify that tables should be joined using an operator other than equal (=) | Modifying Join Operators |
| Specify that joined tables should include rows even when they don't match rows in the corresponding table | Creating Outer Joins |
| Use a join to find subsets of data within a single table | Creating Self-Joins |
| Remove a join between tables | Removing Joins |