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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: