Q

IN subquery or EXISTS subquery

I have a query below that I would like to change into a NOT EXITS to see if it would be more efficient. How would I convert the below query into a COUNT with NOT EXITS?


select count(distinct(serv_item.serv_item_id))
from serv_item 
where serv_item.status = '6'
and serv_item.serv_item_id not in (select pbi_bill_activation.tbs_id from pbi_bill_activation where pbi_bill_activation.tbs_id_type in ('EUL','OFFER','S800I','SCRDI','SDIDI'));

Here is a sample NOT IN query:

select * 
  from MyTable
  where MyCol not in ( select MyCol from MyOtherTable )
The following query has the same results, though it uses a NOT EXISTS. Not the sub-query is correlated.
select * 
  from MyTable 
  where not exists ( select * from MyOtherTable 
                       where MyCol = MyTable.MyCol )
Although you could easily rewrite your query using my example, I'll do it here to further illustrate the concept.
select count(distinct(serv_item.serv_item_id))
from serv_item 
where serv_item.status = '6'
and not exists ( select pbi_bill_activation.tbs_id 
                   from pbi_bill_activation 
                   where pbi_bill_activation.tbs_id_type in
                   ('EUL','OFFER','S800I','SCRDI','SDIDI') 
                     and pbi_bill_activation = 
                         serv_item.serv_item_id )

For More Information


This was first published in September 2002
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close