Records missed by SELECT query (bug)

        3 votes: *****      4,470 views      0 comments
by Allen Browne, 20 April 2005    (Access 2000+)

Flaws in Microsoft Access

Provided by Allen Browne, 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:


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Records missed by SELECT query (bug)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.