Check Whether Related Data Exists by Using Subqueries

To check for the presence or absence of related data, use the EXISTS or NOT EXISTS operators. These operators are part of the WHERE clause that precedes a subquery. A subquery is a query that appears inside another query statement.

These query examples all use a subquery to check on related records to accounts.

• Which accounts don’t have a phone number?
• Which accounts have a phone number but no email address?
• How many accounts have a phone number but no email address?

For example, this query uses a subquery to return the accounts that don’t have a phone number.

SELECT "ssot__Id__c",
       "ssot__Name__c"
FROM "ssot__Account__dlm"
WHERE NOT EXISTS (
 SELECT *
      FROM "ssot__ContactPointPhone__dlm"
 WHERE "ssot__ContactPointPhone__dlm"."ssot__PartyId__c" = "ssot__Account__dlm"."ssot__Id__c"
)

This output shows the first few rows of the sample results.

You can use these predicates with subqueries.

• EXISTS
• NOT EXISTS
• IN