Why does my left join in Access have fewer rows than the left table?

database, ms-access, sql

Solution

This should never happen. Unless rows are being inserted/deleted in the meantime,

the query:

SELECT *
FROM a LEFT JOIN b
         ON whatever ;

should never return less rows than:

SELECT *
FROM a ;

If it happens, it's a bug. Are you sure the queries are exactly like this (and you have't omitted some detail, like a `WHERE` clause)? Are you sure that the first returns 4149 rows and the second one 4103 rows? You could make another check by changing the `*` above to `COUNT(*)`.

Problem

I have two tables in an MS Access 2010 database: TBLIndividuals and TblIndividualsUpdates. They have a lot of the same data, but the primary key may not be the same for a given person's record in both tables. So I'm doing a join between the two tables on names and birthdates to see which records correspond. I'm using a left join so that I also get rows for the people who are in TblIndividualsUpdates but not in TBLIndividuals. That way I know which records need to be added to TBLIndividuals to get it up to date. ``` SELECT TblIndividuals.PersonID AS OldID, TblIndividualsUpdates.PersonID AS UpdateID FROM TblIndividualsUpdates LEFT JOIN TblIndividuals ON ( (TblIndividuals.FirstName = TblIndividualsUpdates.FirstName) and (TblIndividuals.LastName = TblIndividualsUpdates.LastName) AND (TblIndividuals.DateBorn = TblIndividualsUpdates.DateBorn or (TblIndividuals.DateBorn is null and (TblIndividuals.MidName is null and TblIndividualsUpdates.MidName is null or TblIndividuals.MidName = TblIndividualsUpdates.MidName)))); ``` TblIndividualsUpdates has 4149 rows, but the query returns only 4103 rows. There are about 50 new records in TblIndividualsUpdates, but only 4 rows in the query result where OldID is null. If I export the data from Access to PostgreSQL and run the same query there, I get all 4149 rows. Is this a bug in Access? Is there a difference between Access's left join semantics and PostgreSQL's? Is my database corrupted (Compact and Repair doesn't help)?

Original source