正如维基百科关于连接的文章中提到的here http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join, 对于样本表
[员工]
LastName DepartmentID
---------- ------------
Heisenberg 33
Jones 33
Rafferty 31
Robinson 34
Smith 34
Williams NULL
和[部门]
DepartmentID DepartmentName
------------ --------------
31 Sales
33 Engineering
34 Clerical
35 Marketing
完全外连接
SELECT *
FROM employee FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
可以使用三个 SELECT 语句的 UNION ALL 来模拟。所以,在 Access 中你可以这样做
SELECT dbo_employee.LastName, dbo_employee.DepartmentID,
dbo_department.DepartmentName, dbo_department.DepartmentID
FROM dbo_employee
INNER JOIN dbo_department ON dbo_employee.DepartmentID = dbo_department.DepartmentID
UNION ALL
SELECT dbo_employee.LastName, dbo_employee.DepartmentID,
NULL, NULL
FROM dbo_employee
WHERE NOT EXISTS (
SELECT * FROM dbo_department
WHERE dbo_employee.DepartmentID = dbo_department.DepartmentID)
UNION ALL
SELECT NULL, NULL,
dbo_department.DepartmentName, dbo_department.DepartmentID
FROM dbo_department
WHERE NOT EXISTS (
SELECT * FROM dbo_employee
WHERE dbo_employee.DepartmentID = dbo_department.DepartmentID)
但是,由于您在 SQL Server 中使用链接表,因此您只需使用 Access 传递查询并使用 T-SQL 执行“真正的”FULL OUTER JOIN:
传递查询总是会生成不可更新的记录集,但是针对使用 UNION ALL 的链接表的本机 Access 查询将生成不可更新的记录集,因此为什么不利用仅使用 SQL 的速度和简单性服务器运行查询?