"SELECT TOP", "LEFT OUTER JOIN", "ORDER BY" gives extra rows

I have the following Access 2002 query I'm running through OLE DB in .NET:

SELECT  TOP 25 
        tblClient.ClientCode, 
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region

There are 431 records within tblClient that have RegionCode set to NULL.

For some reason, the query above returns all these 431 records instead of the first 25.

If I change the query to ORDER BY tblClient.Client (the name of the client) like so:

SELECT  TOP 25
        tblClient.ClientCode,
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblClient.Client

I get the expected result set 开发者_运维技巧of 25 records, showing a mixture of region names and NULL values.

Why is it that ordering by a field retrieved through a LEFT OUTER JOIN will the TOP clause not work?

EDIT: Possible solution

When I also ORDER BY tblClient.Client the query appears to work:

SELECT  TOP 25
        tblClient.ClientCode,
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region, tblClient.Client

Since I don't really mind if I sort by a second field, for now I will do this.

The behaviour you are seeing is not because the field is retrieved through a LEFT OUTER JOIN it is because the field is NULL

NULL in SQL does not behave like any other value

If a and b are both NULL then a = b is false. Thus when comparing for grouping access sees all the NULLs as different values

In this case if you want to use TOP you could exclude the NULL values by adding

WHERE tblRegion.Region IS NOT NULL

I've seen this before and then it was because Access only returned 25 rows if the 25th column used in the ORDER BY was unique. If it recurred Access also returned the tied values meaning that it can return more than 25 rows in one ORDER BY and exactly 25 in another.

So if the end of the ORDER BY hits NULL it would show all tied (NULL) values. This kind of bug is probably fixed in newer versions of Access, but asI don't have Access on this machine, but you could try:

select top 5 {1,2,3,4,5,5,5,5} ascending and descending to see if it applies to your version of Access.

HTH

I don't have Access installed or your schema for that matter but does this work?

SELECT  TOP 25 
        tblClient.ClientCode, 
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY NZ(tblRegion.Region,'')

When I also ORDER BY tblClient.Client the query appears to work:

SELECT  TOP 25
        tblClient.ClientCode,
        tblRegion.Region
FROM    (tblClient LEFT OUTER JOIN
            tblRegion ON tblClient.RegionCode = tblRegion.RegionCode)
ORDER BY tblRegion.Region, tblClient.Client

Since I don't really mind if I sort by a second field, for now I will do this.

I've updated by question to reflect this as a possible solution.