How to replace the IN operator with EXISTS operator, for the where clause part of the query?

Question

WHERE ( 
((SERVICECOMPONENT_ID IN (123, 150, 198, 199, 290, 287, 291, 289, 288, 286, 282, 281)))  

OR ((SERVICEREQUEST_ID IN ( 
        SELECT distinct(SR.SERVICEREQUEST_ID) 
        FROM SERVICE_REQUEST SR,ASSIGNED_SR_PROJECTS ASP,PROJECT_RESOURCES PRS 
        WHERE SR.SERVICEREQUEST_ID = ASP.SERVICEREQUEST_ID 
        AND PRS.PROJECT_ID = ASP.PROJECT_ID 
        AND PRS.RESPONSIBILITY IN ('MANAGER','LEAD') 
        AND PRS.RESOURCE_ID =180 )) )

)

The 'EXISTS' condition is for checking if something is present in a sub-query, so you would need to change your code to reference a sub-query instead of a list. The power of google...[http://docs.oracle.com/cd/E11882_01/server.112/e41084/conditions012.htm#SQLRF52167][1] — Jared, Jul 08 '15 at 15:54
I was referring to the IN operator in the OR part of the where condition . Any help is appreciated , thx in advance — Muscle Nerd, Jul 08 '15 at 15:57

reaanb · Accepted Answer · 2015-07-08T16:16:25.830

In general,

SELECT a FROM b WHERE c IN (SELECT d FROM e)

is equivalent to

SELECT a FROM b WHERE EXISTS (SELECT 1 FROM e WHERE c = d)

The SERVICEREQUEST_ID IN (subquery) part of your code example translates to:

OR EXISTS (
    SELECT 1
    FROM
        SERVICE_REQUEST SR,
        ASSIGNED_SR_PROJECTS ASP,
        PROJECT_RESOURCES PRS
    WHERE
        SR.SERVICEREQUEST_ID = ASP.SERVICEREQUEST_ID
        AND PRS.PROJECT_ID = ASP.PROJECT_ID
        AND PRS.RESPONSIBILITY IN ('MANAGER', 'LEAD')
        AND PRS.RESOURCE_ID = 180
        AND mytable.SERVICEREQUEST_ID = SR.SERVICEREQUEST_ID
)

score 1 · Answer 2 · edited May 23 '17 at 12:21

if you have static list of elements It's better to use "IN".... If you have a subquery and it is returning more than one value then Use Exist... There is no Difference in Both clauses..

WHERE ( 
 ( (SERVICECOMPONENT_ID IN (  123  ,   150  ,   198  ,   199  ,   290  ,   287  ,   291  ,   289  ,   288  ,   286  ,   282  ,   281 )) )  
 OR ( (SERVICEREQUEST_ID IN (  1952  ,  2387  ,  3618  ,  3633  ,  4178  ,  4432  ,  5090  ,  5271  ,  6068  ,  6320  ,  6396  ,  6526  ,  7162  ,  7442  ,  7558  ,  7639  ,  7688  ,  8176  ,  8189  ,  8338  ,  8460  ,  8461  ,  8598  ,  8612  ,  8628  ,  8675  ,  8775  ,  8869  ,  8886  ,  8898  )) )  
 OR ( (REQUESTED_BY LIKE 'XXXXXXX@example.com' ) )  
 OR ( ( EXISTS ( SELECT count(distinct(SR.SERVICEREQUEST_ID)) FROM SERVICE_REQUEST SR,ASSIGNED_SR_PROJECTS ASP,PROJECT_RESOURCES PRS WHERE SR.SERVICEREQUEST_ID = ASP.SERVICEREQUEST_ID AND PRS.PROJECT_ID = ASP.PROJECT_ID AND PRS.RESPONSIBILITY IN ('MANAGER','LEAD') AND PRS.RESOURCE_ID =180 )) )  
 OR ( (STATUS_CODE LIKE 'OPEN' ) AND ( EXISTS (SELECT count(COMPONENT.CATEGORY_ID) FROM PROJECTMASTER PROJECTS, BUDGET BUDGET, CONTRACT CONTRACT,COMPONENTS COMPONENT, PROJECT_RESOURCES PROJ_RESOURCES, CATEGORY_OWNER_ASSIGNMENT CATEGORYOWNER, SERVICECATEGORYASSIGNMENT CATEGORYASSIGNMENT WHERE PROJECTS.PROJECT_ID = PROJ_RESOURCES.PROJECT_ID AND PROJECTS.BUDGET_ID = BUDGET.BUDGET_ID AND BUDGET.CONTRACT = CONTRACT.CONTRACT_ID AND CATEGORYASSIGNMENT.CONTRACT_ID = CONTRACT.CONTRACT_ID AND COMPONENT.COMPONENT_ID = CATEGORYASSIGNMENT.COMPONENT_ID AND CATEGORYOWNER.CATEGORY_ID = COMPONENT.CATEGORY_ID AND CATEGORYOWNER.USER_ID = PROJ_RESOURCES.RESOURCE_ID AND (CATEGORYOWNER.OWNER_FLAG = 'Y' OR CATEGORYOWNER.MEMBER_FLAG = 'Y') AND PROJ_RESOURCES.RESOURCE_ID = 180 AND PROJ_RESOURCES.ACTIVE_FLAG = 'Y' AND CATEGORYASSIGNMENT.ACTIVE_FLAG = 'Y' AND PROJ_RESOURCES.RESPONSIBILITY IN ('MANAGER', 'LEAD') )) ) 
 )

read this for further clarification.. Difference between EXISTS and IN in SQL?

How to replace the IN operator with EXISTS operator, for the where clause part of the query?

2 Answers2