SOQL Relationships : In our previous Salesforce Tutorial we have learned about  SOQL Functions. In this Salesforce Training Tutorial we are going to learn about SOQL Relationships.

SOQL Relationships between objects are mainly used to query the records from one or more objects in a single SOQL statement in salesforce.com.

Different SOQL Relationships.

  1. SOQL Relationships between Standard objects.
  2. SOQL Relationships between custom objects.
  3. Many-to-one relationships: (n:1)

SOQL Relationships between Standard objects.

Parent-to-Child Relationship.

  1. When a relationship is from parent-to-child then SOQL statement must have sub Query in it’s Statement.
  2. For standard objects SOQL relationships name is equal to plural of the Child Object name.
SOQL Relationships

  • SELECT name, ( SELECT Lastname FROM CONTACTS ) FROM Account.

SOQL RelationshipsChild-to-Parent Relationship.

  1. When a SOQL Relationship is from Child-to-Parent then the SOQL statement will not have Sub Query.
  2. SOQL Relationships name is equal to name of the parent object.
  • SELECT contact.firstname, contact.Account.name FROM Contact.
SOQL Relationships

Many-to-one relationships: (n:1)

Child-to-Parent relationship

These relationships are specified in SELECT, FROM, WHERE clauses using dot(.) operator.

  • SELECT id, Name, Account.name FROM Contact WHERE Account.Industry = ‘ Media’.

Parent-to-Child Relationship

  • SELECT name (SELECT lastname FROM Contacts) FROM Account.

SOQL Relationships between custom objects.

Parent-to-Child relationship.

  • SELECT Lastname__c, ( SELECT Lastname__c FROM Daughters__r ) FROM Mother__c.

Child-to-Parent relationship.

  • SELECT Id, Firstname__c, Mother__r.firstname__c FROM Daughter__c WHERE Mother__r.Lastname__c LIKE ‘c%’.

Interview Questions on SOQL Relationships

Here are some common interview questions on SOQL Relationships in Salesforce:

  1. What is a SOQL relationship query, and why is it used?
    Answer: SOQL relationship queries allow you to retrieve related records from multiple objects in a single query. They are used to fetch data from parent-to-child or child-to-parent relationships, optimizing data retrieval and minimizing the need for multiple queries.
  2. What is the difference between a parent-to-child and a child-to-parent SOQL query?
    Answer: In a parent-to-child query, you retrieve child records related to a parent record using a subquery (e.g., SELECT Name, (SELECT LastName FROM Contacts) FROM Account). In a child-to-parent query, you retrieve parent records directly from a child record by using the relationship name (e.g., SELECT Name, Account.Name FROM Contact).
  3. How do you perform a child-to-parent query in SOQL?
    Answer: A child-to-parent query uses the child object as the starting point and retrieves fields from the parent by using the relationship name. For example, SELECT Name, Account.Name FROM Contact retrieves each Contact’s name and its related Account’s name.
  4. How do you retrieve related child records in a parent-to-child relationship in SOQL?
    Answer: To retrieve related child records, you perform a subquery on the child object within the main query on the parent object. For example, SELECT Name, (SELECT LastName FROM Contacts) FROM Account retrieves each Account and its related Contacts.
  5. Can you perform multiple child subqueries in a single SOQL query?
    Answer: Yes, you can include multiple child subqueries within a single parent query. For example, SELECT Name, (SELECT LastName FROM Contacts), (SELECT Subject FROM Cases) FROM Account retrieves each Account’s related Contacts and Cases.
  6. What are the limitations of relationship queries in SOQL?
    Answer: Relationship queries have limits, such as the maximum number of child records returned in a subquery (2,000 records), and cannot traverse more than five levels of parent-to-child relationships. Complex queries with multiple relationships may also be limited by governor limits.
  7. What is the purpose of using aliases in SOQL relationship queries?
    Answer: Aliases are used in SOQL to make fields and subqueries easier to reference, especially in complex queries. They help improve readability and can be useful in debugging and formatting the results.
  8. Can you filter on related fields in a SOQL relationship query?
    Answer: Yes, you can filter related fields in SOQL. For example, SELECT Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE LastName = 'Smith') filters Accounts based on conditions in their related Contacts.
  9. What is the maximum number of child-to-parent relationships that can be queried in SOQL?
    Answer: SOQL allows you to traverse up to five levels of parent relationships in a single query. For example, querying a grandparent from a child object is allowed.
  10. How would you retrieve Contacts along with their parent Account information and each Account’s related Opportunities in a single SOQL query?
    Answer: You would use a combination of child-to-parent and parent-to-child queries, like this:
    sql SELECT Name, Account.Name, (SELECT Name FROM Account.Opportunities) FROM Contact

These questions assess an understanding of SOQL relationship queries, their usage, limitations, and efficiency in retrieving related data in Salesforce.