Re: SQL Problem w/MINUS

Are you sure you want to use the MINUS clause here? It sounds more like you want to use the INTERSECT clause. You want all customer numbers where they have ordered more than ten AND are under 30. Then you'd write it something like:

select cust_nbr from customer where age < 30 intersect
select cust_nbr from customer where amount >=10;

But if this is truly the case, then you'd be better off performing a join as Roman suggested.

Any time you use MINUS or INTERSECT, you will require sorting to carry out the operation. With large tables (14m & 60m), this can be a very time-consuming operation. You might be better served using the join as it may (or may not) run faster.

HTH,
Brian

Steve Fuller wrote:
>
> Hi All,
>
> I am using Oracle 8.1.6 on a Linux platform and I am finding unwanted
> results from a SQL statement that uses the
> MINUS clause.
>
> select cust_nbr from customer where age >= 30
> minus
> select cust_nbr from order where amount >= 10
>
> The customer table has about 14m rows and the order table has 60m rows.
> I am trying to select all customers that have a single purchase of 10 or
> more.
> At the end I run reports on the result set and I get customers that are less
> than 30
> Questions
> <1> Is there a better way write this select?
> <2> Are there any problems with using the MINUS clause
> <3> Are there any bugs related to SQL within Oracle for Linux
>
> Thanks
>
> Steve Fuller
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================