Received: from out4.ibm.net (out4.ibm.net [165.87.194.239]) by hub.org (8.9.1/8.9.1) with ESMTP id XAA13780 for ; Mon, 23 Nov 1998 23:54:01 -0500 (EST) (envelope-from pierre@desertmoon.com) Received: from [129.37.120.245] (slip129-37-120-245.mo.us.ibm.net [129.37.120.245]) by out4.ibm.net (8.8.5/8.6.9) with SMTP id EAA50170 for ; Tue, 24 Nov 1998 04:53:57 GMT Message-Id: <199811240453.EAA50170@out4.ibm.net> Subject: MINUS and slow 'not in' Date: Mon, 23 Nov 1998 22:53:57 -0600 x-mailer: Claris Emailer 2.0v3, January 22, 1998 From: pierre To: Mime-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" All, I've got a small problem. Say you have tables A and B. They both have a userid column. Table B was selected and previously filled with entries from table A. Lets say about 2000 out of 40,000. Now I want to select everything from A that isn't in B, so about 38,000 entries. I can't seem to get the MINUS to work within a select statement all I ever get are parse errors. Is this even implemented yet? I then tried using a 'not in' clause. select * from A where user_id not in (select * from B); This is VERY slow, and examining the explain output tells me that it will use the user_id index for table B, but a sequential scan of A even though A has an index for the user_id column. Am I missing something? Does anyone have any ideas? Thanks for any help. -=pierre