Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท SOQL and DML

Relationship Queries in SOQL

Query related records in SOQL: child-to-parent dot notation, parent-to-child subqueries, custom relationship names, semi-joins and anti-joins, with Java stand-ins.

Real data is connected: a Contact belongs to an Account, an Opportunity has Line Items, a Case has Comments. In SQL you would write a JOIN. SOQL has no JOIN keyword; the relationships are already defined in the data model, and you walk them with dot notation or subqueries. Understanding the two directions is the key.

SOQL cannot run here. Real queries are read-only; the stand-ins are Java code that shows the shape of the result.

Child to parent: dot notation

When you query a child (the record with the lookup field), you can read fields of its parent by following the relationship name with a dot:

SQL
SELECT Id, LastName, Account.Name, Account.Industry
FROM Contact
WHERE Account.Industry = 'Energy'

You can also filter on parent fields, as shown. You may go up to five levels (Contact.Account.Owner.Manager.Name).

For standard relationships the name is the field name without Id: AccountId becomes Account. For custom lookups the name is the field's API name with __c replaced by __r: Region__c becomes Region__r.

SQL
SELECT Name, Region__r.Name FROM Invoice__c

In Apex you read the parent like any object: contact.Account.Name. If the parent field is empty, contact.Account is null, so use contact.Account?.Name.

Parent to child: subqueries

When you query a parent, put a nested SELECT in parentheses to bring its children. The FROM in the subquery uses the plural child relationship name (for standard objects, usually the plural object name):

SQL
SELECT Id, Name, (SELECT Id, LastName FROM Contacts), (SELECT Name FROM Opportunities)
FROM Account
WHERE Industry = 'Energy'

In Apex each account carries its children as a list:

Java
List<Account> accounts = [
    SELECT Name, (SELECT LastName FROM Contacts)
    FROM Account
    WHERE Industry = 'Energy'
];
for (Account a : accounts) {
    System.debug(a.Name + ' has ' + a.Contacts.size() + ' contacts');
}

This is one query for all the accounts and all their contacts. That is exactly why you never need a query inside a loop to fetch children. Limits: up to 20 subqueries per query, and the total rows retrieved still count toward the 50,000 limit. For custom relationships the child name ends in __r: (SELECT Name FROM Line_Items__r).

flowchart LR
    C["Contact (child)"] -- "Account.Name<br/>(dot notation)" --> A["Account (parent)"]
    A -- "(SELECT ... FROM Contacts)<br/>(subquery)" --> C

Dot notation goes up from child to parent. A subquery goes down from parent to children.

A stand-in for both directions

In Java we can model one parent holding a list of children. Going from child to parent is a field reference; going from parent to child is a list.

Java Stand-in (Java): parent and child traversal

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Semi-joins and anti-joins

A semi-join filters parents by the existence of related children, using a subquery in the WHERE:

SQL
-- accounts that have at least one open opportunity
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)

-- accounts with NO contacts (anti-join)
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Contact)

This is far cheaper than looping accounts in Apex and checking each one. The inner query must return a single Id field.

The stand-in below models a semi-join and an anti-join with sets.

Java Stand-in (Java): semi-join and anti-join with sets

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Polymorphic fields

Some lookups point to several object types. Task.WhoId can be a Contact or a Lead; Task.WhatId can be an Account, Opportunity or Case. To read fields of the parent, use TYPEOF or Who.Name/What.Name:

SQL
SELECT Subject, Who.Name, What.Name FROM Task

Use Who.Type to find out which kind of record it is.

Common mistakes

  • Using Contact.Account.Name in a subquery's own scope: relationship names are relative to the object in the FROM of that (sub)query.
  • Using Contacts for a custom child instead of Line_Items__r.
  • Forgetting that a subquery returns a (possibly empty) list, not null.
  • Assuming a parent lookup is always filled.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which query reads the Account name for each Contact?

    Child-to-parent queries use dot notation with the relationship name.
  2. 2. What is the relationship name for a custom lookup field Region__c?

    Custom relationship names replace the __c suffix with __r.
  3. 3. How many queries does fetching accounts with their contacts via a subquery use?

    A parent-to-child subquery is a single SOQL query.
  4. 4. What does WHERE Id NOT IN (SELECT AccountId FROM Contact) return?

    It is an anti-join: accounts whose Id is not among the contacts' AccountId values.

Summary

  • Go up from child to parent with dot notation; go down from parent to children with a subquery.
  • Standard relationship names drop Id; custom ones end in __r.
  • A relationship query is still one query toward your limit.
  • Semi-joins (IN) and anti-joins (NOT IN) filter parents by their children.
  • Always guard against missing parents before reading their fields.

Next: Aggregate SOQL