You receive incorrect results from an Outer Join query (275058)

SELECT Employees.LastName, Employees.FirstName, Orders.OrderID
FROM Employees LEFT JOIN [SELECT * FROM  Orders WHERE ShipCity = 'Warszawa']. AS Orders 
ON Employees.EmployeeID = Orders.EmployeeID;
				

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 Northwind
GO
SELECT Employees.LastName, Employees.FirstName, Orders.OrderID
FROM Employees LEFT OUTER JOIN Orders
ON ((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.OrderID
FROM 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.OrderID
   FROM Employees LEFT OUTER JOIN Orders
   ON ((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.