title: Access INSERT statement using INNER JOIN with NOT EXISTS gives wrong result
body: <p>I have an Access .mdb database.  There are two tables with identical structures: Prices and tmpPrices.  Each table has three columns: [As of Date std] (Date/Time), Price (double), CUSIP (text, 255 chars).  tmpPrices contains new records to be added to Prices.  I have two SQL queries to update Prices from tmpPrices, one uses a WHERE join and the other uses an INNER JOIN.</p>
  
  <p>The following version A works correctly:</p>
  
  <pre><code>INSERT INTO [Prices] SELECT * FROM [tmpPrices] WHERE NOT EXISTS 
  (SELECT * from [Prices]
    WHERE ([Prices].[As of date std] = [tmpPrices].[As of date std])
    AND ([Prices].CUSIP = [tmpPrices].CUSIP));
  </code></pre>
  
  <p>whereas this version B does not work:</p>
  
  <pre><code>INSERT INTO [Prices] SELECT * FROM [tmpPrices] WHERE NOT EXISTS
  (SELECT  [Prices].* FROM [Prices] INNER JOIN [tmpPrices] ON 
     ([Prices].[As of Date std] = [tmpPrices].[As of Date std]) 
     AND ([Prices].CUSIP = [tmpPrices].CUSIP));
  </code></pre>
  
  <p>Both sub-queries give the same result: a subset of records from tmpPrices that already exist in Prices.  </p>
  
  <p>It would not matter too much using version A were it not that I have to update other tables and one other table takes around 45 minutes to update using version A but only a fraction of that using version B and it appears to work correctly.  So I would like to understand what is going on here.</p>