really?

to me, the WHERE just does not look right:

I see two huge issues:

first, each EXISTS seem to reference THREE tables, and there's no joins...i don't even think it should work:

WHERE EXISTS (SELECT

dbo.ACCOUNT.PORTFOLIO

FROM dbo.ACCOUNT

WHERE dbo.CLIENT.REGION = 'R1')

that's dbo.ACCOUNT from the inner query (maybe?) and dbo.CLIENT.REGION from the outer query, with no criteria to join the two within the EXISTs.,

the same thing with the second EXISTS :

SELECT

dbo.ACCOUNT.PORTFOLIO

FROM dbo.ACCOUNT

WHERE dbo.DM_SERVICES.SERVICE = 'MISSING_SERVICE')

where is dbo.DM_SERVICES joined to dbo.ACCOUNT?

both of those end up being a cross join, whihch i assume makes it slow, and probably never filters much data.

The other thing is, for an exists, i always select a constant, and not a column name for clarity.

i would select a constant in my exists, instead of the column from the outer query;

something like this looks a little better to me.

SELECT

acct.PORTFOLIO

FROM dbo.ACCOUNT acct

INNER JOIN dbo.SERVICE_JOIN

ON acct.PORT_ROW_ID = dbo.SERVICE_JOIN.PORTFOLIO_ID

INNER JOIN dbo.SERVICES

ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.SERVICES.ROW_ID

INNER JOIN dbo.CONTACT_JOIN

ON dbo.SERVICE_JOIN.SERVICE_ID = dbo.CONTACT_JOIN.SERVICE_ID

INNER JOIN dbo.CONTACTS

ON dbo.CONTACT_JOIN.CONTACT_ID = dbo.CONTACTS.ROW_ID

FULL OUTER JOIN dbo.NOTES

ON dbo.SERVICES.SERVICE_TYPE = dbo.NOTES.SERVICE_TYPE

AND acct.ACC_ROW_ID = dbo.NOTES.ACCOUNT_ID

--testing if related data exists or not

WHERE EXISTS (SELECT

1

FROM dbo.CLIENT cl

WHERE cl.REGION = 'R1'

AND cl.?PORTFOLIO? = acct.PORTFOLIO)

--testing if related data exists or not

AND NOT EXISTS (SELECT

1

FROM dbo.DM_SERVICES sv

WHERE sv.SERVICE = 'MISSING_SERVICE'

AND sv.?PORTFOLIO? = acct.PORTFOLIO)