Re: Help with a "not in" query

Article View: borland.public.interbase.sql

Article #234

Back to borland.public.interbase.sql Previous Next

#234

From: Hugo
Date: Sun, 15 Jun 2008 22:10

32 lines
1870 bytes

Bill Todd [TeamB] escribi�:
> Hugo wrote:
> 
>> select * FROM TABLE_1
>> 	WHERE ID_VENTA = 20081398
>> 	AND OPCION = 1
>> 	AND ID_ARTICULO NOT IN (
>> 		SELECT ID_ARTICULO FROM TABLE_2
>> 		WHERE ID_VENTA = 20081398
>> 		AND OPCION = 1);
> 
> A left outer join is usually the fastest way to perform a "not in"
> query. The best performance should occur if you have an index on
> A.ID_VENTO + A.OBCION and an index on B.ID_ARTICULO.
> 
> SELECT A.* FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B
> ON A.ID_ARTICULO = B.ID_ARTICULO
> WHERE A.ID_VENTA = 20081398
> AND A.OBCION = 1
> AND B.ID_ARTICULO IS NULL
> 
> If the query is still slow post the plan and the metadata, particularly
> the indices, for both tables.
> 

Ok, I've tried it and it's faster, but what about if I want to delete 
the the records from the first table? I just cannot replace "select * " 
for  "delete"

Regards,

Hugo.

Technical Details

Message-ID: <48559360$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!proxad.net!feeder1-2.proxad.net!194.25.134.126.MISMATCH!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

References: <48558e17$1@newsgroups.borland.com> <4855914f$1@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,82d9b914a86c4cad
X-Google-Attributes: gid114a0e,domainid0,public,usenet
X-Google-Language: ENGLISH,ASCII
Date: Mon, 16 Jun 2008 00:10:38 +0200
From: Hugo <hugo@hugohiram.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
Newsgroups: borland.public.interbase.sql
Subject: Re: Help with a "not in" query
References: <48558e17$1@newsgroups.borland.com> <4855914f$1@newsgroups.borland.com>
In-Reply-To: <4855914f$1@newsgroups.borland.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 77.224.139.205
Message-ID: <48559360$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1213567840 77.224.139.205 (15 Jun 2008 15:10:40 -0700)
Lines: 32
Path: arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!proxad.net!feeder1-2.proxad.net!194.25.134.126.MISMATCH!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail

Reply to Parent Message