You receive incorrect results from an Outer Join query

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q275058
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS
When you run a Left Outer Join or Right Outer Join query, Microsoft Access may return incorrect data.
CAUSE
This behavior occurs when Access runs a SQL-92-compliant Outer Join query with a WHERE clause. Access incorrectly interprets the syntax of such a query to be an Inner Join query.
RESOLUTION
To resolve this issue, obtain the latest Microsoft Jet 4.0 service pack that contains an updated version of the Microsoft Jet 4.0 database engine.

For additional information about how to obtain the latest version of the Jet 4.0 database engine, click the following article number to view the article in the Microsoft Knowledge Base:
239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
To work around this issue, break the query into two steps. The subquery is a Select query with the WHERE condition. It is wrapped in the main query that performs the actual left or right join operation. The following sample code is based on the procedure described in the "Steps to Reproduce the Problem" section of this article:
SELECT Employees.LastName, Employees.FirstName, Orders.OrderIDFROM Employees LEFT JOIN [SELECT * FROM  Orders WHERE ShipCity = 'Warszawa']. AS Orders ON Employees.EmployeeID = Orders.EmployeeID;				
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in Jet 4.0 Service Pack 4 (SP4) and later versions.
MORE INFORMATION
Unlike the INNER JOIN statement, the OUTER JOIN statement returns all records from one table, even if they have no matching records in the related table. A Left Outer Join returns all rows from the table on the left side of the LEFT OUTER JOIN statement and only those records from the table on the right side of the statement that meet the relational search criteria. A Right Outer Join returns all rows from the table on the right side of the RIGHT OUTER JOIN statement and only those records from the table on the left side of the statement that meet the relational search criteria.

For example, the following query, based on the Northwind sample database, uses the SQL-92-compliant Outer Join syntax. It retrieves all employee names from the Employees table and only those OrderIDs from the Orders table where the destination city (ShipCity) is "Warszawa":
USE NorthwindGOSELECT Employees.LastName, Employees.FirstName, Orders.OrderIDFROM Employees LEFT OUTER JOIN OrdersON ((Employees.EmployeeID = Orders.EmployeeID) and (Orders.ShipCity = 'Warszawa'))				
The preceding query, when run on the Microsoft SQL Server computer, correctly returns the following 11 rows:
   LastName        FirstName   OrderID   -----------------------------------   Davolio         Nancy       10374   Davolio         Nancy       10792   Fuller          Andrew      NULL   Leverling       Janet       NULL   Peacock         Margaret    10906   Peacock         Margaret    11044   Buchanan        Steven      10870   Suyama          Michael     10611   King            Robert      NULL   Callahan        Laura       10998   Dodsworth       Anne        NULL				
Note that for the employees who never had any orders going to Warszawa, the OrderID column contains NULL. This represents the correct results according to the SQL-92 standard.

When the same query is run from Access, it returns the following seven rows
   Last Name       First Name  Order ID   ------------------------------------   Davolio         Nancy       10374   Suyama          Michael     10611   Davolio         Nancy       10792   Buchanan        Steven      10870   Peacock         Margaret    10906   Callahan        Laura       10998   Peacock         Margaret    11044				
which is equivalent to the following inner join query:
SELECT Employees.LastName, Employees.FirstName, Orders.OrderIDFROM Employees INNER JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID)WHERE (Orders.ShipCity = 'Warszawa')				
Note that all rows containing NULL in the Order ID column are missing. For the purpose of this example, Access converts the query to an Inner Join query with the same WHERE condition. As a result, only matching records are returned.

Steps to Reproduce the Problem

  1. Start Access and open the sample database Northwind.mdb.
  2. In the Database window, click Queries.
  3. Double-click Create Query in Design View.
  4. In the Show Table dialog box, click Close.
  5. On the Query Design toolbar, click SQL, and then type or paste the following query:
    SELECT Employees.LastName, Employees.FirstName, Orders.OrderIDFROM Employees LEFT OUTER JOIN OrdersON ((Employees.EmployeeID = Orders.EmployeeID) and (Orders.ShipCity = 'Warszawa'))					
  6. On the Query menu, click Run.Note that only those records with associated OrderIDs are returned. The correct results should return all employees from the Employees table, even if they have no corresponding records in the Orders table.
REFERENCES
For more information about outer joins, visit the following Microsoft Developer Network (MSDN) Web site:
pra ACC2002 reviewdocid
Properties

Article ID: 275058 - Last Review: 12/05/2015 21:59:35 - Revision: 7.5

Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kbbug kbnofix KB275058
Feedback