Обсуждение: IN() alternatives

I thought I saw a doc somewhere showing alternatives to using IN() for better performance, but can't find it :(. Are there better performing query alternatives to this?

delete from tab1 where id in (select id2 from tab2 where ...);

Where the subquery returns 1-10K's of records.

John

-----Original Message-----
From: John Smith [mailto:john_smith_45678@yahoo.com]
Sent: Wednesday, February 05, 2003 1:03 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] IN() alternatives

I thought I saw a doc somewhere showing alternatives to using IN() for better performance, but can't find it :(. Are there better performing query alternatives to this?

delete from tab1 where id in (select id2 from tab2 where ...);

Where the subquery returns 1-10K's of records.

John

---

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

-----Oorspronkelijk bericht-----
Van: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Namens Dann Corbit
Verzonden: woensdag 5 februari 2003 22:12
Aan: John Smith; pgsql-general@postgresql.org
Onderwerp: Re: [GENERAL] IN() alternatives

Unless the subquery is correlated, you can reformulate it as a join.

-----Original Message-----
From: John Smith [mailto:john_smith_45678@yahoo.com]
Sent: Wednesday, February 05, 2003 1:03 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] IN() alternatives

I thought I saw a doc somewhere showing alternatives to using IN() for better performance, but can't find it :(. Are there better performing query alternatives to this?

delete from tab1 where id in (select id2 from tab2 where ...);

Where the subquery returns 1-10K's of records.

John

---

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

On Wed, Feb 05, 2003 at 01:02:53PM -0800, John Smith wrote:
>
> I thought I saw a doc somewhere showing alternatives to using IN() for better performance, but can't find it :(. Are
therebetter performing query alternatives to this? 
>
> delete from tab1 where id in (select id2 from tab2 where ...);

Depending on what you're doing, postgres has an extension of SQL

DELETE FROM tab1 WHERE id=tab2.id2 AND tab2.somefield [some
condition].

At least, this has worked for me.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Hi John

See chapter 6.15 Subquery Expressions.

Regards
Conni