Seeking Help Optimizing Self-Joins This query takes ~5x longer than it should due to the WHERE IN() statement at the end. I am a junior DBA in over my head trying to figure out a way to optimize it. Is there a way to check IN() the resultset of the outer query? Can any of the self-joins be combined? Are there any query optimization resources you would recommend? 

Apologies for the lack of minimalism, completeness, and verifiyibility - including the DDL seemed like overkill. Below are the query and the execution plan bottleneck.

SELECT SC.ROW_ID      C1_CASE_ID,
       SC2.ROW_ID     C2_NEW_CASE_ID,
       SC2.CASE_NUM   C3_NEW_CASE_NUM,
       MC.ROW_ID      C4_MSTR_CASE_ID,
       MC2.ROW_ID     C5_NEW_MSTR_CASE_ID,
       MC2.CASE_NUM   C6_NEW_MSTR_CASE_NUM
 FROM  siebel.ODS_S_CONTACT          MCON2, 
       siebel.ODS_S_CONTACT          MCON, 
       siebel.ODS_S_CASE             SC, 
       siebel.ODS_S_CASE             MC, 
       siebel.ODS_S_CASE_BNFTPLAN    CBP, 
       siebel.ODS_S_CASE             MC2, 
       siebel.ODS_S_CASE             SC2
WHERE (SC.STATUS_CD = 'Withdrawn')
  AND (CBP.STATUS_CD IN ('Active', 'Approved','Inactive') 
  AND EXISTS (
               SELECT 1 
                 FROM (SELECT cbp2.row_id cbp2_row_id, 
                              cbp2.case_id cbp2_case_id,
                              DENSE_RANK() OVER (PARTITION BY cbp2.case_id ORDER BY CASE WHEN cbp2.status_cd = 'Active' THEN 1
                                                                                         WHEN cbp2.status_cd = 'Approved' THEN 2
                                                                                         WHEN cbp2.status_cd = 'Inactive' THEN 3
                                                                                         ELSE 4 
                                                                                    END,cbp2.created DESC
                                                ) cbp2_order
                         FROM siebel.ods_s_case_bnftplan cbp2
                      --where cbp2.case_id = CBP.CASE_ID
                      ) sq
                WHERE cbp2_order = 1
                  AND CBP.CASE_ID = cbp2_case_id
                  AND CBP.ROW_ID = cbp2_row_id
             )
      )
WHERE (SC.MSTR_CASE_ID=MC.ROW_ID)
  AND (MC.APPLICANT_ID=MCON.ROW_ID)
  AND (MCON.SOC_SECURITY_NUM=MCON2.SOC_SECURITY_NUM AND MCON.ROW_ID &lt;&gt; MCON2.ROW_ID)
  AND (MCON2.ROW_ID=MC2.APPLICANT_ID)
  AND (MC2.ROW_ID=SC2.MSTR_CASE_ID AND SC2.TYPE_CD = SC.TYPE_CD AND SC2.STATUS_CD = 'Active')
  AND (SC.ROW_ID=CBP.CASE_ID)
  AND SC2.CREATED IN (
                        -- default: 46 rows  ~30seconds
                        SELECT MAX(SCSUB.CREATED)
                          FROM siebel.ODS_S_CASE          SC2SUB
                         INNER JOIN siebel.ODS_S_CASE    MC2SUB        ON MC2SUB.ROW_ID = SC2SUB.MSTR_CASE_ID
                         INNER JOIN siebel.ODS_S_CONTACT MCON2SUB      ON MC2SUB.APPLICANT_ID = MCON2SUB.ROW_ID
                         INNER JOIN siebel.ODS_S_CONTACT MCONSUB       ON MCON2SUB.SOC_SECURITY_NUM = MCONSUB.SOC_SECURITY_NUM
                         INNER JOIN siebel.ODS_S_CASE    MCSUB         ON MCONSUB.ROW_ID = MCSUB.APPLICANT_ID
                         INNER JOIN siebel.ODS_S_CASE    SCSUB         ON MCSUB.ROW_ID = SCSUB.MSTR_CASE_ID 
                         INNER JOIN siebel.ODS_S_CASE_BNFTPLAN CBPSUB  ON CBPSUB.CASE_ID = SCSUB.ROW_ID
                         WHERE SC2SUB.ROW_ID = SC2.ROW_ID
                           AND  CBPSUB.STATUS_CD IN ('Active', 'Approved')
                           AND SC2SUB.TYPE_CD = SCSUB.TYPE_CD 
                           AND SCSUB.STATUS_CD = 'Active'
                      )


Execution Plan here.
It looks like you have very few indexes in there.  If this isn't a third-party app then here are some indexes I'd test out.  I'm making some assumptions, such as Row_ID is a unique column.

CREATE UNIQUE CLUSTERED INDEX CIX_OdsSCase_Sc2Sub_RowId ON ODS_S_Case.Sc2Sub
(
    RowId
)

CREATE INDEX IX_OdsSContact_Mcon2Sub_SocSecurityNum ON ODS_S_Contact.MCON2SUB
(
    Soc_Security_Num
)
INCLUDE
(
    Row_Id
)

CREATE INDEX IX_OdsSCaseBnftplan_Cbpsub_CaseId ON ODS_S_CASE_BNFTPLAN
(
    CASE_ID
)
INCLUDE
(
    STATUS_CD
)

CREATE INDEX IX_OdsSContact_Mconsub_SocSecurityNum ON ODS_S_CONTACT.MCONSUB
(
    Soc_Security_Num
)
INCLUDE
(
    Row_Id
)

CREATE INDEX IX_OdsSCase_Scsub_StatusCd_MstrCaseId ON ODS_S_CASE.SCSUB
(
    Status_Cd
    , Mstr_Case_Id
)
INCLUDE
(
    Row_Id
    , Created
    , Type_Cd
)


Also, I can't see this without saying something.  You appear to have a social security number unencrypted, used as a foreign key, assuming it's unique, etc..  This is a risk on many levels.  If what I'm seeing is correct and you're not convinced it's a bad idea  you should read https://www.computerworld.com/article/2552992/not-so-unique.html and https://helifromfinland.blog/2014/04/18/is-social-security-number-a-good-primary-key/

I've spent too much time on this for now.  Try out these indexes in your test environment and see how much of a difference it makes.  If you're able to make these types of changes and didn't know you should then start looking for blog posts introducing people to indexing.