how do i check with SOQL if any objects in list fulfilling condition

Question

I have this soql query

SELECT Id, Name, 
    (SELECT ContactPointAddress.Id, ContactPointAddress.IsDefault FROM ContactPointAddresses)
FROM Account

which list all contact point address for each account. Can i somehow add a where clause so i can get the accounts that does not have any contact point address or where the list of contactpointaddresss dont have one where isDefeault = true

Answer 1

"anti joins" (NOT IN), like "show me accounts that don't have any contacts, why do we even bother keeping them" would be

SELECT Id, Name 
FROM Account 
WHERE Id NOT IN (SELECT AccountId FROM Contact)

You can do similar thing in reports, cross filter.

Try

WHERE Id NOT IN (SELECT AccountId FROM ContactPointAddress WHERE IsDefault = true)