Help with a "not in" query

Thread View: borland.public.interbase.sql

6 messages

[T] Back to Threads [V] Tree View [G] Group Articles

6 total messages Started by Hugo Sun, 15 Jun 2008 21:48

#233

Author: Hugo
Date: Sun, 15 Jun 2008 21:48

38 lines
1686 bytes

Hello

I am having trouble wit a query, I want to select all the records from 
table_1 that not exist in table_2

Some fields on TABLE_1 and TABLE_2 have to be the same, like 
ID_ARTICULO, ID_VENTA and OPCION

TABLE_1 has 35000+ records

TABLE_2 has 28500+ records

I have tried these two methods, but IBconsole stop responding on both 
methods.



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);


SELECT * FROM TABLE_1 T1
	WHERE T1.ID_VENTA = 20081398
	AND T1.OPCION = 1
	AND NOT EXISTS (
		SELECT * FROM TABLE_2 T2
		WHERE T2.ID_VENTA = 20081398
		AND T2.OPCION = 1
		AND T2.ID_ARTICULO = T1.ID_ARTICULO);

Any help will be appreciated, regards.

Hugo.

Re: Help with a "not in" query

#232

Author: Bill Todd [TeamB]
Date: Sun, 15 Jun 2008 22:01

25 lines
1654 bytes

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.

-- 
Bill Todd (TeamB)

Re: Help with a "not in" query

#234

Author: 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.

Re: Help with a "not in" query

#235

Author: Bill Todd [TeamB]
Date: Sun, 15 Jun 2008 22:39

8 lines
1347 bytes

Your original post did not mention deleting records.

DELETE FROM TABLE_1
WHERE ID_ARTICULO IN
(SELECT ID_ARTICULO FROM...)

-- 
Bill Todd (TeamB)

Re: Help with a "not in" query

#238

Author: Hugo
Date: Mon, 16 Jun 2008 16:58

26 lines
1508 bytes

Bill Todd [TeamB] escribi�:
> Your original post did not mention deleting records.
> 
> DELETE FROM TABLE_1
> WHERE ID_ARTICULO IN
> (SELECT ID_ARTICULO FROM...)
> 


True

Sorry, first I wanted to make a select to understand it better, So, it 
would be something like:


DELETE 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);


regards,
Hugo.

Re: Help with a "not in" query

#239

Author: Bill Todd [TeamB]
Date: Mon, 16 Jun 2008 17:27

18 lines
1684 bytes

What I was suggesting was:

DELETE FROM TABLE 1 
WHERE ID_ARTICULO IN
(SELECT A.ID_ARTICULO 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)

The select creates a list of all ID_ARTICULOs from TABLE_1 that have
the specified ID_VENTA and OBCION values and which do not have a
matching row in TABLE_2. It then deletes all of the rows in TABLE_1
whose ID_ARTICULO is in the list returned by the select. I think that
is what you want.

-- 
Bill Todd (TeamB)

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads