Not exists in sql query
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&gt; select * from customer c where c.customerid not in (select p.customerid from purchasebill p);

no rows selected


Now with NOT EXISTS:

SQL&gt; 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&gt; 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&gt; 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&gt; 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