Re: SQL questions???

>In article <6m22em$vke1_at_hkpa05.polyu.edu.hk>,
> "Elton Chan" <97980015r_at_polyu.edu.hk> wrote:
>>
>> Hi all,
>>
>> Anybody know how to replace 'NOT IN' operator by other operations?
>>
>> SELECT * FROM TABLE1 WHERE
>> CODE1 NOT IN (
>> SELECT * FROM TABLE1 WHERE
>> ( CODE1 = '1' OR CODE1 = '2' OR CODE1 = '3' )
>> AND
>> ( CODE2='A' OR CODE2='B' OR CODE2='C')
>> );
>>
>> ANY clue??? I've tried to use outer join, but it cannot be used with OR
>> operation...
>>
>> Thanks,
>> Elton
>>

You can also use the MINUS operator - just like the union, but it removes records.

Select * from table1
minus select * from table1 where
(code1='1' or code1='2' or code1='3' ) and (code2='A' or code2='B' or code2='C')

Incidently, the OR statement will prevent the use of an index.If you have a big table and you are worried about performance, this can be a problem. Since you can always remove another row, you can make a longer list of MINUSes. In addition, you can improve the sorting for unique records by only returning the primary key.

select * from table1 where
pk_field in
(select pk_field from table1 minus
select pk_field from table1 where

   code1=1 and code2='A'
MINUS
select pk_field from table1 where

   code1=1 and code2='B'
minus
select pk_field from table1 where

   code1=1 and code2='C'
MINUS
 select pk_field from table1 where
   code1=2 and code2='A'
select pk_field from table1 where

   code1=2 and code2='B'
...

You get the idea... It is a lot longer, but the response time will probably be much better.

Dan Hekimian-Williams Received on Tue Jun 30 1998 - 22:13:13 CDT