Hi A., On 26 Sep 2001 07:24:41 -0700, [EMAIL PROTECTED] (A. Mannisto) wrote:
>Hello, > >does anybody know why this: >SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2) > >equals this: >SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = >col2) > >but this: >SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2 WHERE >col3='huu') > >equals _NOT_ this: >SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 = >col2 AND col3='huu') > >E.g. resultset is not the same in last two statements. >Can I get same set as IN statement somehow using EXISTS (performance >issue)? I cannot reproduce your problem, results are equal here with PostgreSQL 7.1.3. Could you post your CREATE TABLE and INSERT statements? Re performance: There's more than one way to do it. (Where did I hear this before? ;-)) You might try: SELECT tab.* FROM tab, tab2 WHERE tab.col1 = tab2.col2; or SELECT DISTINCT ... , if col2 is not unique in tab2. Kind regards, Carl van Tast ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
