Ask the Expert

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'));

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top