Not exists in sql query

oracle-11g

I'm using oracle database

I need to display customer id and customer name of those customers who have not purchased at all from any retail outlet using not exists

My query:

select customerid,customername
from customer c
where  not exists (
select p.customerid
from purchasebill p,retailoutlet r,customer c
where p.customerid=c.customerid and p.retailoutletid=r.retailoutletid group by p.customerid);

I supposed to get 3 records but i'm getting output as no rows selected, instead of not exists if i use not in, the output is correct.What's wrong in the above code please let me know.

Best Answer

First of all, NOT IN is not the same as NOT EXISTS.

create table customer (customerid number);
create table purchasebill (customerid number, retailoutletid number);
create table retailoutlet (retailoutletid number);

insert into customer values (1);
insert into customer values (2);

insert into purchasebill values (1, 1);
insert into purchasebill values (null, 3);

insert into retailoutlet values (1);
insert into retailoutlet values (3);

commit;

Let's find customers without purchasebill (customerid = 2). FIrst with a naive NOT IN:

SQL> select * from customer c where c.customerid not in (select p.customerid from purchasebill p);

no rows selected

Now with NOT EXISTS:

SQL> select * from customer c where not exists (select 1 from purchasebill p where p.customerid = c.customerid);

CUSTOMERID
----------
         2

NOT IN did not provide the result we expected. It's because the way NOT IN works. If any row of the subquery returns NULL, NOT IN will return FALSE, so no result in this case.

Now your queries:

SQL> select * from customer c where not exists (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

no rows selected

SQL> select * from customer c where customerid not in (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2

The problem with the NOT EXISTS query is that you have customer c both in the outer query and the subquery. The subquery uses customer c from its inside, not the customer c from the outer query. Because of this, the subquery always returns data, so NOT EXISTS will always evaluate to FALSE, hence no result.

The NOT IN version works, because the inner query is not a correlated subquery, it does not use customer c from the outside, but the inside. The inner query returns customers who made purchases, without NULLs, because the join to purchasebill p eliminates them. With no NULLs to worry anymore, NOT IN works as naively expected.

If you want to make NOT EXISTS work, remove customer c from the subquery, so it becomes a correlated subquery:

SQL> select * from customer c where not exists (select 1 from purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2
Related Question