Jody Jody - 3 months ago 4
SQL Question

IN VS EXIST sub queries

why does this work:

select custid, companyname
from sales.customers as c
where exists
(select orderid
from sales.orders as o
where c.custid = o.custid and o.orderdate = '20070212')


and this does not

select custid, companyname
from sales.customers as c
where custid in
(select orderid
from sales.orders as o
where c.custid = o.custid and o.orderdate = '20070212')


and when should I use what?

Answer

In your first query with the exists clause, it actually doesn't matter what you are selecting in the subquery, it just checks for the existence of the row in the subquery. You could have selected NULL and it would work just as well:

select custid, companyname
  from sales.customers as c
 where exists (select null -- doesn't matter what you select
                 from sales.orders as o
                where c.custid = o.custid 
                  and o.orderdate = '20070212')

However, in your 2nd query with the in clause, the column being returned in the subquery is significant. And though we don't know anything about your table structure and data, the column names make it pretty clear that you are selecting the wrong column in the subquery (... where custid in (select orderid ...). (Why do you compare customer ids to order ids?)

In fact, if you are going to use an in clause, it doesn't really make sense to also have a correlated subquery, it's kind of redundant. So it would probably look like this instead:

select custid, companyname
  from sales.customers as c
 where c.custid in (select o.custid
                    from sales.orders as o
                   where o.orderdate = '20070212')

Obviously, I don't know much about your data, but for this type of query, using the exists clause with a correlated subquery will normally be the better choice. As with anything, check and compare the different execution plans and choose the best one.

As for general guidelines about when you would select in vs. exists, there are tons of great articles out there already, including on SO. Not a difficult search on google.

Comments