1
select * 
from product 
where productname in  (select productname 
                       from product 
                       where ProductQty = 89)

select * 
from product 
where exists  (select productname 
               from product 
               where ProductQty = 89)

In above 2 queries I think result should be same but both the queries are displaying different result set. Can someone explain why this is happening?

Exists query is displaying all of the records that were in the table.

3

They are doing two different things.

The first one displays products where the ProductQty is equal to 89. It is essentially the same as:

select * 
from product 
where ProductQty = 89
  and productname is not null;

The second one displays all products (regardless of their quantity) if there is at least one product with ProductQty = 89 because the sub-query is not related to the outer query.

An exists (or not exists) without a co-related subquery is usually an error. I think what you intended to write was:

select p1.*
from product p1
where exists  (select *
               from product p2
               where p2.ProductQty = 89
                 and p1.productname = p2.productname) --<< make it a co-related sub-query
  • If we consider the second query, than in that whatever the subquery throws as a result on that basis the outer query will throw results? – user11497433 Jul 2 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.