>> I'd like to fetch all the rows in "code" that don't exist in >> "companies": >> >> SELECT code.id FROM code,companies WHERE code.id IS NOT IN (SELECT >> code FROM companies); > > select code.id from code > where code.id not in (select code from companies);
A technically better (eg faster) solution is to use "exists" (which only looks for the first instance) rather than "in" (which scans the whole table unnecessarily): select Code.ID from Code where not exists (select 1 from Companies where Companies.Code = Code.ID) ; Another solution is: select id from code except select code from companies ; HTH, Tom BareFeet -- SQLite GUI apps compared: http://www.tandb.com.au/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
