Relationship Filters: Semi-Joins and Anti-Joins

The previous lesson reached up from a Contact to its Account. This lesson turns the filter around: you decide which Accounts you want by looking at the Contacts hanging off them. The Accounts are the rows you return, but the Contacts decide which ones come back.

The Semi-Join

A semi-join filters the outer query by a condition on a child object. It is a subquery inside an IN clause:

List<Account> accounts = [
	SELECT Name
	FROM Account
	WHERE Id IN (SELECT AccountId FROM Contact WHERE LeadSource LIKE :pattern)
	ORDER BY Name
];

Read it from the inside out. The inner query runs first and returns a list of Account Ids: every Account that has at least one Contact whose LeadSource matches. The outer query then returns the Accounts whose Id is in that list.

Two details are load-bearing. The inner FROM names the child OBJECT, not the relationship: write FROM Contact, not FROM Contacts. The plural spelling fails with sObject type 'Contacts' is not supported. And the child field never appears in the outer WHERE clause, because LeadSource is a field on Contact, not on Account. Put it there and the query fails with No such column 'LeadSource' on entity 'Account'.

Each Parent Comes Back Once

A semi-join returns one row per matching PARENT, however many children matched. When two Contacts share an Account, that Account appears exactly once. There is no DISTINCT keyword to write, and you should not build the answer out of the child rows either: query the Contacts and read Account.Name off each one and the Account repeats once per matching Contact.

The Anti-Join

Swap IN for NOT IN and the direction flips. The outer query now keeps the rows that have no matching child:

List<Account> accounts = [
	SELECT Name
	FROM Account
	WHERE Id NOT IN (SELECT AccountId FROM Contact WHERE LeadSource LIKE :pattern)
];

There is a sharp edge here, and it is not the one you would guess. In standard SQL a single NULL inside a NOT IN list makes the whole condition return no rows at all. In SOQL those NULLs are ignored: the anti-join still returns every Account except the ones whose Id really is in the list. The rule was probed on the validation org, where a Contact with no Account (so a null AccountId) in the subquery changed nothing.

Common Mistakes

The first mistake is the plural child object in the inner FROM. The second is writing the child field in the outer WHERE clause. The third is assuming the outer query is sorted. Without ORDER BY the Accounts come back in whatever order the database likes, and that order is not guaranteed to repeat on the next run or on another org.

Why This Matters

A dashboard that shows "Accounts with a referral contact" is one query of this shape, not one query per Account. The parent-to-child subquery pulls children down; the semi-join filters parents by their children. Between them, most relationship questions need a single query instead of a loop.