Yes, why is it that "IN" is often inefficient when "EXISTS" is correctly
optimized? You would think that a half-decent optimizer would realise
that they're doing the same job.
Anyway, the answer is to use "EXISTS" - it's efficient, and this works
in Informix too! :)
delete from A
where not exists (
select 1
from B
where A.ID = B.A_ID)
Cheers,
Carl Reynolds Ph: +64-9-4154790
CJN Technologies Ltd. Fax: +64-9-4154791
[EMAIL PROTECTED] DDI: +64-9-4154795
PO Box 302-278, North Harbour, Auckland, New Zealand
12 Piermark Drive, North Harbour Estate, Auckland, NZ
Visit our website at http://www.cjntech.co.nz/
> -----Original Message-----
> From: Kerry Sainsbury [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, August 20, 1999 4:24 PM
> To: Multiple recipients of list database
> Subject: Re: [DUG-DB]: sql to delete non-members of a union
>
> --- Peter Hyde <[EMAIL PROTECTED]> wrote:
> > I want to delete from "A" all items which are not currently
> > represented in B. I could use this SQL I think, but I'm conscious
> > of how SLOOOOOOW it would be, because the second query is
> > going to be run for EVERY row in A.
>
> I don't understand why the sub-query would need to be executed once
> for
> every row in 'A'.
>
> Surely a half-decent optimizer should cope with that statement
> sensibly?
>
> Martin's comments high-light some problems too, he says
>
> > BUT: Your db may not allow deleting from a table referenced in a
> > subquery.
>
> Like Informix :-)
> Informix lets you use temporary tables instead, but Interbase doesn't
> *have* temporary tables.
>
> Isn't the SQL "standard" just a delight?
>
> Cheers,
> Kerry "I know I didn't actually help" S
>
> ===
> -- Systems Engineer, Inprise NZ. [EMAIL PROTECTED]
> _______________________________________________________________
> DO YOU YAHOO!?
> Get your free @yahoo.com.au address at http://mail.yahoo.com.au
>
> ----------------------------------------------------------------------
> -----
> New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
application/ms-tnef
