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 last published in September 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close