Re: Postgresql sql query - selecting rows outside a join
От | Arjen van der Meijden |
---|---|
Тема | Re: Postgresql sql query - selecting rows outside a join |
Дата | |
Msg-id | 002a01c3b831$178f4740$3ac15e91@acm обсуждение исходный текст |
Ответ на | Postgresql sql query - selecting rows outside a join (Graham Leggett <minfrin@sharp.fm>) |
Список | 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
В списке pgsql-general по дате отправления: