Sql Exists clause

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published.