Replace IN with EXISTS or COUNT. How to do it. What is missing here ?

Question

I am using IN keyword in the query in the middle of a section. Since I am using nested query and want to replace In with Exists due to performance issues that my seniors have told me might arise.

Am I missing some column, what you are looking for in this query. This query contain some aliases for readibility.

How can I remove it.

SELECT  TX.PK_MAP_ID AS MAP_ID 
        , MG.PK_GUEST_ID AS Guest_Id 
        , MG.FIRST_NAME 
        , H.PK_CATEGORY_ID AS Preference_Id 
        , H.DESCRIPTION AS Preference_Name 
        , H.FK_CATEGORY_ID AS Parent_Id 
        , H.IMMEDIATE_PARENT AS Parent_Name 
        , H.Department_ID 
        , H.Department_Name 
        , H.ID_PATH, H.DESC_PATH 
FROM          
    dbo.M_GUEST AS MG  
        LEFT OUTER JOIN 
    dbo.TX_MAP_GUEST_PREFERENCE AS TX  
            ON  
                (MG.PK_GUEST_ID = TX.FK_GUEST_ID) 
        LEFT OUTER JOIN 
    dbo.GetHierarchy_Table AS H  
            ON  
                (TX.FK_CATEGORY_ID = H.PK_CATEGORY_ID) 
WHERE      
    (MG.IS_ACTIVE = 1)  
        AND  
    (TX.IS_ACTIVE = 1)  
        AND  
    (H.Department_ID IN -----How to remove this IN operator with EXISTS or Count() 
              ( 
                SELECT C.PK_CATEGORY_ID AS DepartmentId 
                FROM  
                    dbo.TX_MAP_DEPARTMENT_OPERATOR AS D  
                        INNER JOIN 
                    dbo.M_OPERATOR AS M  
                            ON   
                                (D.FK_OPERATOR_ID = M.PK_OPERATOR_ID) 
                                    AND  
                                (D.IS_ACTIVE = M.IS_ACTIVE) 
                        INNER JOIN 
                    dbo.L_USER_ROLE AS R  
                            ON   
                                (M.FK_ROLE_ID = R.PK_ROLE_ID) 
                                    AND  
                                (M.IS_ACTIVE = R.IS_ACTIVE) 
                        INNER JOIN 
                    dbo.L_CATEGORY_TYPE AS C  
                            ON  
                                (D.FK_DEPARTMENT_ID = C.PK_CATEGORY_ID) 
                                    AND  
                                (D.IS_ACTIVE = C.IS_ACTIVE) 
                WHERE       
                    (D.IS_ACTIVE = 1)  
                        AND  
                    (M.IS_ACTIVE = 1)  
                        AND  
                    (R.IS_ACTIVE = 1)  
                        AND  
                    (C.IS_ACTIVE = 1)  
            )--END INNER QUERY 
    )--END Condition 

What new problems might I get if I replace IN with EXISTS or COUNT ?

Answer 1

Basically, as I understand your question, you are asking how can I replace this:

where H.department_id in (select departmentid from...)

with this:

where exists (select...) 

or this:

where (select count(*) from ...) > 1

It is fairly straight forward. One method might be this:

WHERE...
  AND EXISTS (select c.pk_category_id 
              from tx_map_department_operator d
                inner join m_operator as m
                  on    d.fk_operator_id = m.pk_operator_id
                inner join l_user_role l
                  on m.fk_role_id = r.pk_role_id
                inner join l_category_type c
                  on d.fk_department_id = c.pk_category_id
               where h.department_id = c.pk_category_id
                 and d.is_active = 1 
                 and m.is_active = 1 
                 and r.is_active = 1 
                 and c.is_active = 1
              )

I removed the extra joins on is_active because they were redundant. You should test how it runs with your indexes, because that might have been faster. I doubt it though. But it is worth comparing whether it is faster to add the join clause (join on ... and x.is_active=y.is_active) or to check in the where clause (x.is_active=1 and y.is_active=1 and z.is_active=1...)

And I'd recommend you just use exists, instead of count(*), because I know that exists should stop after finding 1 row, whereas count probably continues to execute until done, and then compares to your reference value (count > 1).

As an aside, that is a strange column naming standard you have. Do you really have PK prefixes for the primary keys, and FK prefixes for the foreign keys? I have never seen that.