Semijoins Are Only Supported in WHERE Clause

What does this error mean?

SOQL supports two types of subqueries: relationship subqueries in the SELECT clause (e.g., SELECT Id, (SELECT Id FROM Contacts) FROM Account) and semijoins / anti-semijoins in the WHERE clause (e.g., WHERE Id IN (SELECT AccountId FROM Contact)). Attempting to use a semijoin-style subquery in the SELECT clause or other unsupported position triggers this error.

Common Causes

1. Semijoin subquery in SELECT clause

Trying to return subquery results as a standalone column, which is valid SQL but not SOQL: SELECT (SELECT COUNT() FROM Contacts) FROM Account in a non-relationship context.

2. Nested semijoins

SOQL does not support subqueries nested inside other subqueries beyond one level. A WHERE Id IN (SELECT Id FROM X WHERE Id IN (SELECT ...)) pattern will fail.

How to Fix It

Solution 1: Move semijoin to the WHERE clause

-- ❌ INVALID — semijoin in SELECT
SELECT Id, (SELECT COUNT() FROM Contact WHERE AccountId = Account.Id)
FROM Account

-- ✅ CORRECT — semijoin in WHERE
SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE LeadSource = 'Web')

-- ✅ CORRECT — relationship subquery in SELECT (different syntax)
SELECT Id, Name, (SELECT Id, Email FROM Contacts)
FROM Account

Solution 2: Break into two queries in Apex

For complex filtering that would require nested semijoins, use two separate queries in Apex and filter the second using the results of the first.

// Step 1: get the IDs from the inner condition
Set<Id> contactAccountIds = new Set<Id>();
for (Contact c : [SELECT AccountId FROM Contact WHERE LeadSource = 'Web']) {{
    contactAccountIds.add(c.AccountId);
}}

// Step 2: use the set in the outer query
List<Account> accounts = [
    SELECT Id, Name
    FROM Account
    WHERE Id IN :contactAccountIds
];