SOQL Restforce INNER JOIN query

Question

Currently trying to do an INNER JOIN on my Account and Opportunity tables and am running into the following error. I am using Restforce (SFDC maintained Ruby SDK) to run these queries against a client.

Restforce::ErrorCode::MalformedQuery: MALFORMED_QUERY: 
SELECT Id FROM Account INNER JOIN Opportunity ON AccountId =
                             ^
ERROR at Row:1:Column:30
unexpected token: 'JOIN'

The query I am trying to run is as follows:

SELECT Id FROM Account INNER JOIN Opportunity ON AccountId = Account.id WHERE Opportunity.Owner.Email = 'example.com' OR Account.Named_ADR__r.Email = 'example.com' OR Account.Owner.Email = 'example.com'

Not very familiar with SOQL but am struggling to understand how to do this in Salesforce land. Can anybody lend a hand?

Answer 1

There used to be much better documentation on how joins work in SOQL, but I haven't found it since the massive migration that happened a while back.

It seems like what you want here is to include child records in your query, which would work like so:

SELECT
    Id, (SELECT Id FROM Opportunities WHERE Owner.Email = '...')
FROM Account

Answer 2

You should refer to SOQL documentation. Salesforce doesn't allow joins, but allows relationship queries using object driven model. There are ways to retrieve upstream (parents) and downstream records (child) via SOQL queries

Parent:

select id, Account.Name from Contact

Child:

select id, (select id, Firstname from Contacts) from Account

Suggested read: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm