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.
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.
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