> Graham Leggett > > Hi all, > > I am trying to do a query that returns all rows that are > _not_ part of a join, and so far I cannot seem to find a > query that doesn't take 30 minutes or more to run. > > The basic query is "select * from tableA where tableA_id NOT > IN (select tableA_id from tableB)". > > Is there a more efficient way of doing this? Mysql's version to do something like that is, afaik:
SELECT * FROM tableA LEFT JOIN tableB USING(tableA_id) WHERE tableB.tableA_id IS NULL; Perhaps that makes more efficient use of your indices. Another version is something like: SELECT *, COUNT(tableB.*) FROM tableA LEFT JOIN tableB USING(tableA_id) GROUP BY columns_of_tableA HAVING count(tableB.*) = 0; And perhaps a rewrite to use EXISTS (although that is claimed to be similar in speed or even slower as of postgres 7.4) is useful: SELECT * FROM tableA WHERE NOT EXISTS(SELECT * FROM tableB WHERE tableB.tableA_id = tableA.tableA_id) There are a few others, but it all depens on your index structure and table sizes whether they work more efficient or not. Best regards, Arjen ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
