If Postgresql behaves anything like Oracle, this query goes slowly because the whole
userdetails table is getting read by the server every time it reads a row from the
applicationdetails table.
Assuming userid is indexed in userdetails, in Oracle you could use something like:
SELECT AP.applicationid FROM applicationdetails AP
WHERE NOT EXISTS (SELECT userid FROM userdetails where userid = AP.applicationid)
which should only go to the index to verify that there isn't a row in userdetails that
matches once per row in the applicationdetails table.
I'm afraid I don't know if this works for Postgresql.
The outer join solution proposed by Achim M�hler should be portable (although the
syntax he used is Oracle specific I think).
HTH
Simon
>Hi All,
>
>Ihave two tables in postgresql each has around 100,000
>records and i'm executing this query.
>
>
>select applicationid from applicationdetails where
>applicationid not in (select userid from userdetails)
>
>what is wrong with this query?
>
>Is there any other alternative to get the id which are
>not in userdetails
>
>
>
>Thanks in advance,
>Robin.
------------------------------------------------------------
--== Sent via Deja.com ==--
http://www.deja.com/
