php access left join,LEFT JOIN 和 RIGHT JOIN 运算 (Microsoft Access SQL)

LEFT JOIN 和 RIGHT JOIN 运算 (Microsoft Access SQL)LEFT JOIN, RIGHT JOIN Operations (Microsoft Access SQL)

09/18/2015

本文内容

适用于：Access 2013、Office 2013Applies to: Access 2013, Office 2013

在任何 FROM 子句中用于组合源表记录。Combines source-table records when used in any FROM clause.

语法Syntax

FROM table1 [ LEFT | RIGHT ] JOIN table2 ON table1.field1 compopr table2.field2FROM table1 [ [ LEFT | RIGHT ] JOIN table2

ON *table1.field1 * compopr table2.field2

LEFT JOIN 和 RIGHT JOIN 操作包含以下部分：The LEFT JOIN and RIGHT JOIN operations have these parts:

PartPart

说明Description

table1、table2table1 , table2

对其中的记录进行组合的表的名称。The names of the tables from which records are combined.

field1、field2field1 , 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.

compoprcompopr

任何关系比较运算符：“=”、“"”、“"”、“<=”、“>=”或“<>”。Any relational comparison operator: "=," "," "<=," ">=," or "<>."

说明Remarks

通过 LEFT JOIN 操作可以创建一个左外部联接。左外部联接包含两个表中第一个(左)表中的所有记录，即使在第二个(右)表中没有匹配的记录值。Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

通过 RIGHT JOIN 操作可以创建一个右外部联接。右外部联接包含两个表中第二个(右)表中的所有记录，即使在第一个(左)表中没有匹配的记录值。Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

例如，可以将 LEFT JOIN 用于“部门”表(左)和“员工”表(右)，以选择所有部门，包括那些未分配有员工的部门。若要选择所有员工，包括那些未分配到任一部门的员工，就要使用 RIGHT JOIN。For example, you could use LEFT JOIN with the Departments (left) and Employees (right) tables to select all departments, including those that have no employees assigned to them. To select all employees, including those who are not assigned to a department, you would use RIGHT JOIN.

以下示例演示如何通过 CategoryID 字段将“类别”表和“产品”表联接起来。该查询会生成一个含所有类别的列表，包括那些不含任何产品的类别：The following example shows how you could join the Categories and Products tables on the CategoryID field. The query produces a list of all categories, including those that contain no products:

SELECT CategoryName,

ProductName

FROM Categories LEFT JOIN Products

ON Categories.CategoryID = Products.CategoryID;

在以下示例中，CategoryID 是联接字段，但是它不包括在查询结果中，因为它没有包括在 SELECT 语句中。In this example, CategoryID is the joined field, but it is not included in the query results because it is not included in the SELECT statement. 若要包含联接字段，请在 SELECT 语句中输入该字段名，在本例中为 Categories.CategoryID。To include the joined field, include the field name in the SELECT statement — in this case, Categories.CategoryID.

备注

若要创建一个只包括在联接字段中具有相同数据的记录，请使用 INNER JOIN 操作。To create a query that includes only records in which the data in the joined fields is the same, use an INNER JOIN operation.

LEFT JOIN 或 RIGHT JOIN 可以嵌套在 INNER JOIN 中，但 INNER JOIN 无法嵌套在 LEFT JOIN 或 RIGHT JOIN 中。请参阅 INNER JOIN 主题中有关嵌套的内容，了解如何将联接嵌套在其他联接中。A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins.

可以链接多个 ON 子句。请参阅 INNER JOIN 主题中有关链接字句的内容，了解如何链接字句。You can link multiple ON clauses. See the discussion of clause linking in the INNER JOIN topic to see how this is done.

如果尝试联接包含 Memo 或 OLE 对象数据的字段，则会出错。If you try to join fields containing Memo or OLE Object data, an error occurs.

示例Example

本示例：This is an example.

假设 Employees 表中存在假想的 Department Name 和 Department ID 字段。This example assumes the existence of hypothetical Department Name and Department ID fields in an Employees table. 请注意，这些字段实际上在 Northwind 数据库 Employees 表中不存在。Note that these fields do not actually exist in the Northwind database Employees table.

选择所有部门，包括那些没有员工的部门。This example selects all departments, including those without employees.

调用 EnumFields 过程，您可以在 SELECT 语句示例中找到该过程。This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub LeftRightJoinX()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to Northwind

' on your computer.

Set dbs = OpenDatabase("Northwind.mdb")

' Select all departments, including those

' without employees.

Set rst = dbs.OpenRecordset _

("SELECT [Department Name], " _

& "FirstName & Chr(32) & LastName AS Name " _

& "FROM Departments LEFT JOIN Employees " _

& "ON Departments.[Department ID] = " _

& "Employees.[Department ID] " _

& "ORDER BY [Department Name];")

' Populate the Recordset.

rst.MoveLast

' Call EnumFields to print the contents of the

' Recordset. Pass the Recordset object and desired

' field width.

EnumFields rst, 20

dbs.Close

End Sub