From: | "Arjen van der Meijden" <acmmailing(at)vulcanus(dot)its(dot)tudelft(dot)nl> |
---|---|
To: | "'Graham Leggett'" <minfrin(at)sharp(dot)fm>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgresql sql query - selecting rows outside a join |
Date: | 2003-12-01 17:32:22 |
Message-ID: | 002a01c3b831$178f4740$3ac15e91@acm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2003-12-01 17:34:07 | Re: Postgresql sql query - selecting rows outside a join |
Previous Message | Pavel Stehule | 2003-12-01 17:31:51 | Re: Postgresql sql query - selecting rows outside a join |