Re: "SELECT .. WHERE NOT IN" query running for hours

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Γιωργος Βαλκανας wrote:

Are there any particular semantics for the "NOT IN" statement that cause the correlated query to execute for every row of the outter query, as opposed to the "NOT EXISTS" ? Or are there any other practical reasons, related to "IN / NOT IN", for this to be happening? Or is it simply due to implementation details of each RDBMS? I guess the former (or the 2nd one), since, as you say, this is common in most databases, but I would most appreciate an answer to clarify this.

Thanks again!

Best regards,
George




Well, I really hoped that Bruce, Robert or Greg would take on this one, but since there are no more qualified takers, I'll take a shot at this one. For the "NOT IN (result of a correlated sub-query)", the sub-query needs to be executed for every row matching the conditions on the driving table, while the !EXISTS is just a complement of join. It's all in the basic set theory which serves as a model for the relational databases.

--

Mladen Gogala Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions




--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux