Parent-to-Child Relationships: Subqueries

An Account has Contacts. An Account has Opportunities. A Contact belongs to an Account. SOQL can walk those links in both directions, and the direction that catches new developers is the one that starts at the parent: how do you get the children of a row you already have?

The Subquery Shape

A parent-to-child subquery is a nested query sitting in the select list of the outer query. It runs once per parent row, and its results arrive attached to that parent record:

List<Account> accounts = [
	SELECT Name, (SELECT LastName FROM Contacts ORDER BY LastName)
	FROM Account
	WHERE Name = :accountName
	LIMIT 1
];

for (Contact c : accounts[0].Contacts) {
	System.debug(c.LastName);
}

Three parts matter. The inner query selects from the child object. FROM Contacts is the relationship name, and relationship names are plural, so FROM Contact fails with Did not understand relationship 'Contact' in FROM part of query call. The outer query filters the parent rows, and the inner query sees only the children of the parent row it was matched against.

Reading the Children Off the Parent

The children land in a relationship property named after the child object, so Contacts on an Account is a List<Contact>. Walk it with a for-each loop and copy the fields you need into your own list. The subquery does not hand you one flat list of children; it hands you one list per parent row. With LIMIT 1 you get exactly one parent, and exactly one child list hanging off it.

Three Rules That Bite

Aggregates do not work inside a subquery. COUNT() fails with COUNT() can only be used with root queries, and COUNT(Id) fails with only root queries support aggregate expressions. If you need a count of the children, query the child object directly instead of reaching for a subquery.

You cannot order by a subquery result. ORDER BY total DESC where total is a subquery alias fails with No such column 'total' on entity 'Account', because the alias is a label on the result row, not a column of the parent.

And a subquery result comes back in whatever order the database feels like. Without its own ORDER BY, the children of an Account arrive in no guaranteed order, and that order can differ between orgs and between runs. Put the ORDER BY inside the subquery, not on the outer query.

Common Mistakes

The plural relationship name is the first slip, because FROM Contact reads more naturally than FROM Contacts but does not compile.

The second is reaching for accounts[0] without checking that the query found anything. A query that matches no Account returns an empty list, and accounts[0] on an empty list throws List index out of bounds: 0. Guard with isEmpty() first.

The third is assuming the children arrive sorted because the outer query is sorted. The outer ORDER BY sorts parent rows; it has no effect on the order inside each child list.

Why This Matters

A detail panel on an Account shows its Contacts, its Opportunities, and its Cases. That is one parent-to-child subquery each, not a loop of separate queries. Knowing the shape of the relationship query is what turns N queries into one.