Ms Access 简明教程
MS Access - Joins
数据库是一个数据表集合，这些数据表允许相互进行逻辑关系。你可以使用关系通过表中公共的字段来连接表。一个表可以是任意数量的关系的一部分，但每个关系始终恰好有两个表。在查询中，关系由 Join 表示。
A database is a collection of tables of data that allow logical relationships to each other. You use relationships to connect tables by fields that they have in common. A table can be part of any number of relationships, but each relationship always has exactly two tables. In a query, a relationship is represented by a Join.
What is Join
联接指定如何在一个数据库中的两个或多个表中组合记录。从概念上来说，联接与表关系非常相似。实际上，联接对于查询就像关系对于表一样。
A join specifies how to combine records from two or more tables in a database. Conceptually, a join is very similar to a table relationship. In fact, joins are to queries what relationships are to tables.
以下是本章将讨论的两种基本类型的联接 −
The following are the two basic types of joins that we will be discussing in this chapter −
-
The inner join
-
The outer join
两者都可以轻松地从 queries design view 中创建。
Both of which can easily be created from a queries design view.
Inner Join
现在让我们了解 Inner Join −
Let us now understand Inner Join −
-
The most common type of join is an inner join which is also the default join type in Microsoft Access.
-
Inner Join will display only the rows or records where the joined fields from both tables are equal.
-
This join type looks at those common fields and the data contained within. It only displays the matches.
Outer Join
现在让我们了解 Outer Join 是什么 −
Let us now understand what an Outer Join is −
-
An outer join displays all rows from one table and only those rows or records from the other table where the joined fields are equal.
-
In other words, an outer join shows all rows from one table and only the corresponding rows from the other table.
还有其他联接类型 −
There are other Join types too −
Left Outer Join & Right Outer Join
让我们理解 Left Outer Join 和 Right Outer Join -
Let us now understand Left Outer Join and Right Outer Join −
-
You can choose the table that will display all rows.
-
You can create a Left Outer Join which will include all the rows from the first table.
-
You can create a Right Outer Join that will include all the rows from the second table.
让我们转到 Create 选项卡，从 Design View 创建一个查询。选择 tblProjects 和 tblTasks ，关闭“显示表格”对话框，如图所示。
Let us now go to the Create tab and create a query from the Design View. Select tblProjects and tblTasks and close the Show Table dialog box as in the following screenshot.
添加 tblProjects 中的 ProjectName 字段，以及 tblTasks 表格中的 TaskTitle, StartDate 和 DueDate 。
Add the ProjectName field from tblProjects, and TaskTitle, StartDate and DueDate from tblTasks table.
让我们运行这个查询。
Let us now run the query.
我们只显示了几个项目的记录。一些项目有很多与该项目相关联的任务，这些信息通过 ProjectID 字段关联。
We are only displaying records from a few projects. A couple of these projects have a lot of tasks associated with that project and this information is related through ProjectID field.
-
When we create this query in Microsoft Access, Access is taking this relationship from the relationships we have created.
-
By default, it is creating what’s known as an Inner Join between these two fields, between these two tables, and that is how it is relating this information together from these two tables.
-
It is showing us only the matches, so when we run this query, there are a lot of other projects listed in tblProjects that do not appear as part of our records set in this query, and that is because of how these two tables are joined together, through that Inner Join, which is again, that default Join for any query.
但是，如果你想改变关系，假设你想创建外连接，或者换句话说，显示 tblProjects 的所有项目，该表格中的所有记录以及 tblTasks 中的所有任务——打开连接属性；只需双击关系行即可。
However, if you want to alter the relationship, Let us say you want to create an outer join, or in other words, show all of the projects from tblProjects, every single record that is in that table, along with all of the tasks from tblTasks — Open join properties; we can do this simply by double-clicking on the relationship line.
Access将在连接属性对话框中显示左表格名称和右表格名称。
Access will display the left table name and the right table name in Join Properties dialog.
-
The left column name and the right column name and the first radio button is to only include rows where the join fields from both tables are equal and that is the inner join and that is what is selected by default when creating relationships, when creating a join in the query, but, you can change it.
-
We have two other options as well; we can include all records from tblProjects, and only those records from tblTasks where the joined fields are equal and this one is Left Outer Join.
-
We have a third option, include all records from tblTasks and only those records from tblProjects where the joined fields are equal and this one is Right Outer Join.
这些是你能从“设计视图”轻松创建的不同类型的连接。我们选择第二个选项，即左外连接，然后单击 Ok 。
Those are the different types of joins you can create easily from the Design View. Let us select the second option, which is Left Outer Join, and click Ok.
让我们看看其他步骤 -
Let us now look into the other steps −
当你查看关系行时，你会看到一个指向 tblTasks 的 ProjectID 的小箭头。当你运行此查询时，你将看到以下结果。
When you look at the relationship line, you will see a little arrow pointing towards ProjectID in tblTasks. When you run this query, you will see the following results.
如您所见，它向我们展示了每个项目的名称，无论它是否具有关联的任务。您还将看到许多空字段。所有这些都将为空，因为在这些字段来自的 tblTasks 中没有相关信息。现在，让我们再次转到 Design View ，然后双击关系线。
As you can see that it is showing us every single project name, whether or not it has a related task. You will also see a bunch of null fields. All of this will be blank because there is no related information in tblTasks, where these fields come from. Let us now go to the Design View again and double-click on the relationship line.
在“联接属性”对话框中，选择第三个选项，即右外联接，然后单击 Ok 。
In the Join Properties dialog box, select the third option which is for the Right Outer Join and click Ok.
现在，看看我们的关系线。您将看到一个箭头现在指向 tblProjects 中的 ProjectID 。当您运行此查询时，您将看到以下结果。
Now look at our relationship line. You will see that a little arrow is now pointing towards ProjectID in tblProjects. When you run this query you will see the following results.
Self-Joins
自联接是另一种类型的联接。自联接关联同一表内匹配的字段。例如，查看具有主管字段的员工表，该字段引用存储在同一表内另一个字段（员工 ID）中的同类数字。
Self-join is another type of Join. A Self-join relates matching fields from within the same table. For example, look at the employee’s table with a supervisor field, which references the same kind of number stored in another field within the same table — the employee ID.
如果我们想了解 Kaitlin Rasmussen 的主管是谁，我们将不得不获取存储在该主管字段中的号码并在完全相同的表内该员工 ID 字段中查找它，才能知道 Charity Hendricks 是主管。
If we wanted to know who Kaitlin Rasmussen’s supervisor is, we will have to take the number stored in that supervisor field and look it up within the exact same table in that employee ID field in order to know that Charity Hendricks is the supervisor.
此表不是关系数据库的理想结构，因为它未经过规范化。
This table is not the ideal structure for a relational database because it’s not normalized.
如果我们有一种情况，即我们想创建仅列出员工姓名及其主管姓名的查询，则除非我们创建自联接，否则我们无法轻松查询它。
If we have a situation where we want to create a query that just lists the employee names alongside the names of their supervisors, there is no easy way we can query that unless we create a Self-join.
要查看自联接，请使用以下字段创建一张表并输入一些数据。
To see a Self-join, create a table with the following fields and enter some data.
在这里，我们再次想创建一个包含员工名字和主管姓名的列表。让我们从 query design view 创建查询。
Here we want to create a list again with the first name of the employee and then the name of the supervisor. Let us create a query from the query design view.
现在，添加 tblEmployees 表。
Now, add tblEmployees tables.
关闭此对话框。
Close this dialog box.
现在，为我们的员工添加名字和姓氏。
Now, add the first name and last name for our employees.
我们现在需要一种方法在此表及其自身之间创建关系。为此，我们需要打开 the show table dialog box 并再次添加 tblEmployees。
We now need a way to create a relationship between this table and itself. To do that, we need to open the show table dialog box and add tblEmployees one more time.
我们在本查询视图中创建了相同表的另一份副本。现在，我们需要创建自联接。为此，单击 tblEmployees 表中的主管，按住鼠标按钮，将其直接拖到复制表 tblEmployees_1 中的 EmployeeID 上方。然后，从该复制表中添加名字和姓氏。
We have created another copy of the same table in this query view. Now, we need to create Self-join. To do that, click on Supervisor in tblEmployees table and hold the mouse button and drop it right on top of the EmployeeID in that copied table — tblEmployees_1. Then, add the first name and last name from that copied table.
现在让我们运行您的查询，您将看到以下结果。
Let us now run your query and you will see the following results.
它显示员工的名字和他们主管的名字。这是如何在 Microsoft Access 中创建自联接的方法。
It displays the names of the employees along side the names of their supervisors. And, this is how you create a Self-join in Microsoft Access.