"SELECT TOP", "LEFT OUTER JOIN", "ORDER BY" gives additional lines

Question

I have the following Access 2002 query that I run 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 tblClient in tblClient for which RegionCode set to NULL .
For some reason, the above query returns all these 431 records instead of the first 25.

If I changed the request to ORDER BY tblClient.Client (client name) as follows:

 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 won't this sorting by field obtained through the LEFT OUTER JOIN work in the TOP expression?

EDIT: Possible Solution

When I also ORDER BY tblClient.Client a request appears:

 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 do not mind if I sort by the second field, while I do it.

Answer 1

When I also ORDER BY tblClient.Client a request appears:

 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 do not mind if I sort by the second field, while I do it.

I updated the question to reflect this as a possible solution.

Answer 2

The behavior you see is not because the field is retrieved through the LEFT OUTER JOIN, because the field is NULL

NULL in SQL does not behave like any other value

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

In this case, if you want to use TOP, you can exclude NULL values ​​by adding

 WHERE tblRegion.Region IS NOT NULL 

Answer 3

I saw this before, and then it was because Access only returned 25 rows if the 25th column used in ORDER BY was unique. If it repeats, Access also returns related values, meaning that it can return more than 25 rows in one ORDER BY and exactly 25 in another.

So, if the end of ORDER BY shows NULL, it will display all the NULL values. This error is probably fixed in new versions of Access, but I do not have access to this machine, but you can try:

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

NTN

Answer 4

I do not have access or your circuit, but does it work?

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