0

This query retrieves all the account and the associated contacts for that account

SELECT Id, Name,
  (
    SELECT Id, FirstName, LastName
    FROM Contacts
  )
FROM Account
  WHERE Name like 'Acme%'

This works but I am trying to understand how it works.

Where the joining condition (Account.Id = Contact.AccountId) is specified in the syntax above ?

Is it implicit ?

The relationship "Contacts" is queried and the lookup field on that relationship is the Contact.AccountId. So for every Account record in the SELECT SOQL, Account Id is implicity passed onto the inner query ?

Improve this question
1
  • 2
    It is implicit when you use relationship name you will get child records, But we don't have visibility on how Salesforce translates SOQL to SQL.
    – amitghadage
    Mar 14 at 5:50

1 Answer 1

Reset to default
4

SOQL, "Salesforce Object Query Language", is an API for querying Salesforce Objects. Objects are inherently different than records, in terms of data structure and representation. In many structured database systems, SQL ("Structured Query Language") provides access to structured records (tables, records, and fields). With SOQL, the retrieved data are objects that can express parent and child relationships. This is similar to how systems like GraphQL, NoSQL, and MongoDB are designed. Comparing SOQL directly to SQL is like comparing Java directly to JavaScript. They may have similar-looking names, but they are vastly different in the actual characteristics. Or like the difference between Paris, Texas, and Paris, France. Similar names, completely different places.

So, instead of thinking of the query as implicitly asking for a LEFT JOIN, as we would have to explicitly state in SQL, the SOQL version is instead explicitly asking for the related objects connected to the principle object of the query. The actual magic that goes on in the lower levels of the system do include things like joins, obviously, but they are vastly more complicated than you might realize. The queries have to join and filter against multiple tables, such as sharing settings, user permissions, and more. The SOQL API expresses all of that complicated query logic as a simple object that represents the relationship between two things. As a simple real-life metaphor, if I ask you to list the names of a friend's children, you wouldn't mentally look up the friend, then every child you know, and then use some index table to find the relationships. You'd just recall your friend and their children through the relationship.

At the end of the day, it doesn't matter how the underlying queries are structured. All you need to understand is that we are talking about objects and relationships, not tables in a database. Every object you interact with in the API or the UI has multiple tables that hold all kinds of data. All of that complexity is tucked away, leaving you with a simple interface to find the data you're looking for in a cohesive, organized manner.

Improve this answer
2
  • 1
    Great explanation! Just to add my two cents. SOQL just looks and sounds like SQL but they stand for completely different actual names: Salesforce Object Query Language and Structured Query Language. Everything else was explained really good!
    – Leonid Zadorozhnykh
    Mar 14 at 15:45
  • 1
    @LeonidZadorozhnykh That was in an earlier draft, and I left out the expanded acyronym later for some reason. I guess maybe I felt it was obvious from context, or maybe it was before coffee. Either way, I added that in. Thanks!
    – sfdcfox
    Mar 14 at 16:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .