where exists

Where exists (subquery)

The WHERE EXISTS subquery is used when we want to display all rows where we have a matching column in both tables.  In most cases, this type of subquery can be re-written with a standard join to improve performance.

select
   book_key
from
   book
where 
   exists (select book_key from sales)
;

the following two queries are the same:

    select * from APPS.NPPA_JC_RULES_SETUP
    where exists 
    
    (  select sop_jc_rule , expenditure_category, task_type,  count(1) from  APPS.NPPA_JC_RULES_SETUP
      group by sop_jc_rule , expenditure_category, task_type 
      having count(1) >1
      )
    and org_id <>84

     select * from APPS.NPPA_JC_RULES_SETUP a
     where   exists     
     (select sop_jc_rule , expenditure_category, task_type,count(1) from APPS.NPPA_JC_RULES_SETUP b
     where a.sop_jc_rule= b.sop_jc_rule
     and a.expenditure_category=b.expenditure_category
     and a.task_type=b.task_type
     group by b.sop_jc_rule , b.expenditure_category, b.task_type
     having count(1)>1
     )  
    and org_id <> 84  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23895263/viewspace-680173/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23895263/viewspace-680173/