On May 23, 2012, at 11:12 AM, Paul Sanderson wrote: > I need to return all of the rows in table B that are not present in table A
You have at least 3 ways to express such a query: (1) Using 'in' select table_b.* from table_b where table_b.key not in ( select key from table_a ) (2) Using 'exists' select table_b.* from table_b where not exist ( select 1 from table_a where table_a.key = table_b.key ) (3) Using 'join' select table_b.* from table_b left join table_a on table_a.key = table_b.key where table_a.key is null Additionally, you could also use 'except' to diff the two table keys: select table_b.key from table_b except select table_a.key from table_a > What is the most efficient way of doing this? It mostly depends on your data distribution, relevance of indexes, etc,… check the various query plans and pick the one which fits best... :) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
