ACC2000: Outer Join with WHERE Clause Returns Unexpected Records

This article was previously published under Q208880
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

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

Symptoms
When you run a query based on two tables (in this example, table Aand table B) with an outer join to include all the records from table A,and you apply a WHERE clause to table B, you expect to see all the records from table A and those records from table B that satisfy the WHERE clause. Instead, only those records from table B that satisfy the WHERE clause and have a matching value in table A are returned.
Cause
When you run the query, the outer join is performed first, creating all therecords from table A. Then, the WHERE clause from table B is applied to allthe records, eliminating records from the query. The result set does notcontain all the records from table A, but contains only those for which thecondition is met for table B.

Unlike Microsoft Access, SQL Server currently processes the WHERE beforethe join. This is because this feature was implemented before the ANSISQL-92 standard. Before then, there was no outer join spec at all.Therefore, the same query can return different results against SQLServer, depending on whether you use a Select query or a SQL pass-throughquery (in which case SQL Server handles the query and simply returnsthe results.)
Resolution
If you want to return all the records in table A, you can use two queries.The first query should apply the WHERE clause to table B, and the secondquery should combine table A with the first query to perform the outerjoin.
Status
This type of query processing complies with the ANSI SQL 92 specification.
More information

Steps to Return the Expected Recordset

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Create a new query based on the Products table.
  3. Drag the CategoryID and ProductName fields from the Products field list to the query grid.
  4. In the Criteria row for the ProductName field, type Like A*.
  5. Save the query as Query1, and then close it.
  6. Create a new query based on the Categories table and the Query1 query.
  7. Drag the CategoryName field from the Categories field list to the query grid.
  8. Drag the ProductName field from the Query1 field list to the query grid.
  9. Double-click the join line. In the Join Properties box, click the Include ALL records from 'Categories'..." option button, and then click OK.
  10. Run the query.

    Note that all the records from the Categories table are returned, with the ProductName field filled in for records and with a matching record in the Products table.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Create a new query based on the Categories and the Products tables. The two tables should be joined on the CategoryID field.
  3. Double-click the join line between the two table field lists. In the Join Properties box, select the Include ALL records from 'Categories'... option button, and then click OK.
  4. Drag the CategoryName field from the Categories field list to the first column of the query grid.
  5. Drag the ProductName field from the Products field list to the second column of the query grid.
  6. In the Criteria row of the ProductName column, type Like A*.
  7. Run the query.

    Note that not all the records from the Categories table are returned. Instead, only those records with a product name starting with the letter "A" are returned.
References
For more information about relationships, click Microsoft Access Help on the Help menu, type work with relationships in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
prb
Properties

Article ID: 208880 - Last Review: 10/25/2013 23:48:00 - Revision: 2.0

  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kbprb KB208880
Feedback