Actually, I'm not sure how Oracle or PG would handle this, but in DB2, the nested loop
join you warn against would depend on how the optimizer is set up. It should set up
temp tables and do a hash join, which would be a lot quicker.
-----Original Message-----
From: Simon Legdon [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 29, 2001 6:45 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: What Happenned to this query (sloging)
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/
