Re: Advanced SQL question: NOT EXISTS

> jamesf5088_at_aol.com (JamesF5088) writes:
> From the following tables I would like to find the SALESPERSON who has an
> ORDER with every CUSTOMER ( David in the example) using NOT EXISTS.

NOT EXISTS isn't a good choice -- for anything. It's very inefficient. (If you think about it, you'll realize that it requires that the ENTIRE table be checked for the value.) That being said, I'm not sure that NOT EXISTS is a good choice for what you're attempting to accomplish, either.

> Tables:
> SALESPERSON
> salesperson char(10)
> ORDER
> salesperson char(10)
> customer char(10)
> CUSTOMER
> customer char(10)
 

> The tables are poplulated as follows:
> SALESPERSON
> David
> Susan
> Robert
> ORDER
> David, Customer1
> David, Customer2
> Susan, Customer1
> CUSTOMER
> Customer1
> Customer2

I believe what you want to accomplish is satisfied by the following query:

select salesperson
 from salesperson
where salesperson not in (select salesperson

   from salesperson, order, customer
   where salesperson.salesperson=order.customer(+)

                            order.customer=customer.customer(+)
                                and (customer.customer is null
		       or order.customer is null));

It's a little ugly, and could be made a bit more efficient, but it should do the job at least.

> I've tried the following SELECT statement with no success.
> select salesperson
> from salesperson
> where not exists
> ( select *
> from order
> where not exists
> ( select * from customer
> where customer.customer = order.customer
> and salesperson.salesperson = order.salesperson
> )
> );

This won't return any rows, because you're using an INNER join on the last correlated subquery -- therefore, no rows can satisfy the not exists operator without being eliminated from the query itself.  

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Tue May 23 1995 - 00:00:00 CEST