Re: Select value not in field
->Date: Thu, 3 Feb 94 8:38:20 EST
->From: Anbarnes@letterkenn-emh1.army.mil
->To: informix-list@rmy.emory.edu
->Subject: Select value not in field
->
->Could someone help me with the SQL (NOT 4GL) syntax to select all
->values between x and y which are not in fieldz? For instance, fieldz
->contains all the numbers currently in use on tape labels. I want to
->see which numbers are NOT currently in use, within the available range
->of 0001-9999. The syntax totally escapes me!
->
->Thanks.
->
->Ann Barnes
->---------------------------------------------------------------------------
->Ann Barnes, UNIX System Administrator \\ /\\ /\\
->Letterkenny Army Depot, SDSLE-DOE \\ /\\ & \\/ \\ alumna
->Chambersburg, PA 17201 AV 570-9869/717-267-9869 \\/ \\/ \\
->---------------------------------------------------------------------------
SELECT some_col
FROM some_table
WHERE some_col BETWEEN 0001 AND 9999
AND some_col NOT IN
( SELECT fieldz FROM which_table );
However, unless which_table is rather small, the performance of the above
will be awful. I try to avoid using NOT IN. If you can use a temporary
table, the following has MUCH better performance:
SELECT some_col
FROM some_table
WHERE some_col BETWEEN 0001 AND 9999
INTO TEMP value_list ;
DELETE FROM value_list
WHERE some_col IN
( SELECT fieldz FROM which_table );
SELECT some_col
FROM value_list; { Output from this is equivalent to the first SELECT. }
The performance difference between using IN and NOT IN is orders of magnitude!
Regards,
Alan ___________________________
______________________| R. Alan Popiel |__________________________
\\ Internet: | Martin Marietta, SLS | /
\\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. /
)Voice: | Denver, CO 80201-0179 USA | (
/ 303-977-9998 |___________________________| (But you knew that!) \\
/________________________) (____________________________\\