Re: Postgresql sql query - selecting rows outside a join

> 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