Flaws in Microsoft Access

Provided by allenbrowne.com, January 2004

Records missed by SELECT query

An outer join involving a literal value in the join field behaves as an inner join in Access. You are less likely to experience this flaw than most of those discussed in this section. It is included as it does illustrate a weakness in the query engine (JET) in Access.

An inner join returns only records where there is a match in both tables. For example, an inner join between Customers and Orders returns only the Customers who have Orders. An outer join lets you return all the Customers, along with any orders they have. To change the join type in the Query Design window in Access, double-click the line joining the two tables. Access presents a dialog offering 3 options:

1. Only include rows where the joined fields from both tables are equal.
2. Include ALL records from 'Customers' and only those records from 'Orders' where the joined fields are equal.
3. Include ALL records from 'Orders' and only those records from 'Customers' where the joined fields are equal.

The first option is the inner join. The other two are the left join and right join - the two directions of the outer join.

If the outer join exists on a literal value in a query instead of a field in a table, the outer join does not return all the records that it should. In fact, it omits the Nulls from the outside of the join, and returns only the records where the joined fields from both tables are equal. This behaviour would be correct for an inner join, but is wrong for an outer join.

To demonstrate the flaw with AccessFlaws.zip, open the query named BadSelectQuery. This query is based on another query named BadSelectBase and a table named BadSelect.

The BadSelectBase query contains a calculated field that returns the literal value 1. The BadSelectQuery uses a RIGHT JOIN between that query and the table. The resultant query ought to contain all three records from the table, along with any matches from the stacked query. Instead, it yields only one record from the table.

For the boffins, the SQL statement of the query reads:

SELECT BadSelectBase.MyLiteral, BadSelect.MyText
FROM BadSelectBase RIGHT JOIN BadSelect ON BadSelectBase.MyLiteral = BadSelect.MyNumber;

This is a clear example of Access missing records it ought to return.

There are other examples that result from different implementations of the SQL standard:

• ACC2000: Outer Join with WHERE Clause Returns Unexpected Records
   
• Results Are Not Displayed When You Run a Query That References Linked SQL Server Tables