On 6/29/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
On 6/29/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > You can do it with a JOIN instead of IN and I'm pretty sure it will > still > use an index. > > > SELECT > COUNT(*) > FROM > guid_version_map M > LEFT JOIN > latest_version V > ON > M.guid = V.guid AND M.version = V.version > WHERE > V.guid IS NULL; > > > HTH, > > Sam > > Thanks! This one, and the other solution that involved the correlated sub-query, will likely do exactly what I want. (I'm currently waiting on my production-sized database to copy down from the machine that's hosting it to execute the code on customer-level hardware to measure the performance of each query.)
SQLite 3.3.16 barely breathed hard (less than 300 milliseconds) with this query against a few million rows, detecting the 16 rows that were not present in one table versus the other. Excellent! I'll test it next against 3.4.0 in actual code... but I expect it to operate the same. --andy
