ACC: Outer Join with WHERE Clause Returns Unexpected RecordsLast reviewed: July 18, 1997Article ID: Q124152 |
The information in this article applies to:
SYMPTOMSModerate: Requires basic macro, coding, and interoperability skills. When you run a query based on two tables (in this example, called table A and table B) with an outer join to include all the records from table A and apply a WHERE clause to table B, only those records from table B that have a matching value in table A are returned. This seems to be the reverse of what you expected.
CAUSEWhen you run the query, the outer join is performed first, creating all the records from table A. Then, the WHERE clause from table B is applied to all the records, eliminating records from the query. The result set does not contain all the records from table A, but contains only those where the condition is met for table B. Unlike Microsoft Access, SQL Server currently processes the WHERE before the Join. This is because they implemented this feature before the ANSI SQL-92 standard, and before then, there was no outer join spec at all. Therefore, the same query could return different results against SQL Server depending on whether you use a Select query or a SQL pass-through query (in which case SQL Server would handle the query and simply return the results.)
RESOLUTIONIf 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 second query should combine table A with the first query to perform the outer join.
STATUSThis type of query processing is by design and complies with the ANSI SQL 92 specification.
MORE INFORMATION
Steps to Reproduce Behavior
Steps to Return the Expected Recordset
|
Keywords : kbusage QryJoin
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |