The answers so far have given examples of how you could approximate your given query in SOQL, but don't really go over how the two are connected. You've been given the answer, but nobody has shown their work. I think that this may end up making you more confused than you already are.
This is a very long answer, so get comfortable.
SQL vs SOQL
I don't know if I can explain it very well, but to me, SQL feels more like I'm looking over an entire forest, whereas SOQL only allows me to look at a few specific trees at a time. In a way, I guess that SOQL is closer to an Object-Relational Mapper (ORM) like Hibernate (Java) or Doctrine (PHP) than it is to SQL itself.
In SOQL, you can't join arbitrary tables on arbitrary conditions. A query in SOQL mostly revolves around just a single object. You can bring other tables (sObjects) into the mix, but there are restrictions.
Converting your query
Let's take your example SQL, and start working with it.
First, I'll assume that there is a one-to-many relationship between
B (i.e. there can be many
B records related to a single
A record, or in SQL parlance there is a foreign key to
Further, I'll assume that
B are custom objects in Salesforce, meaning that we'll be using
B__c in the SOQL.
A__c is the base sObject/table in your query, so we start off like this
[SELECT Id FROM A__c]
Even if you leave it off of the query, the Id of the base object is implicitly queried. To my knowledge, this is the only field that is implicitly queried. Everything else needs to be explicitly queried (no wildcards are allowed in the SELECT clause).
If you have record types defined for an SObject, the recordTypeId is also implicitly queried
Next, tackling the join. In the SOQL way of thinking,
B__c is a child object of
A__c. One parent (
A__c) has many children (
B__c). SOQL allows you to query up to one level down the parent-child hierarchy (any more and the volume of the datasets returned would explode. Not a good thing when you have multiple companies sharing Salesforce's server resources).
The 'foreign key' I talked about on
B__c will be one of two types of relationship fields, either a Lookup or a Master-Detail (it doesn't really matter which, for our purposes). The important bit to know is that this is a single field which contains the Id of the parent
A__c record. For ease, let's call the 'foreign key'
A_lookup__c. There is no such thing as a composite key in SOQL.
A relationship between objects in Salesforce also has a 'relationship name', which is typically just the plural of the child object, so
Bs__r in our case.
To traverse down the relationship hierarchy, you use an inner select/subquery. This is what Salesforce calls a Left Outer Join (though
JOIN are not keywords in SOQL).
[SELECT Id, (SELECT Id, Field2__c, Field3__c FROM Bs__r) FROM A__c]
Field1 in your example is the foreign key, notice that we don't explicitly join on that field. Salesforce takes care of that for us when we use the relationship name.
Unfortunately, SOQL cannot compare a field to another field, so we can't take care of the
a.field2 = b.field2 part of your example SQL in the 'equivalent' SOQL. That type of filtering needs to be done in apex, outside of a query.
The first part of the
WHERE clause maps easily between SQL and SOQL. It ends up just being copy/paste. Your equivalent query is now
[SELECT Id, (SELECT Id, Field2__c, Field3__c FROM Bs__r) FROM A__c WHERE Field3__c < 100]
Because SOQL only allows one Object/table to appear in the
FROM clause, there's no need to prefix the field with the object/table name. In fact, aliasing in general is pretty rare in SOQL.
The second part of your
WHERE clause can't be added to the
WHERE clause for the query on
A__c because the field you're referencing is on
A__c (and there are potentially many
B__c records, so Salesforce wouldn't know which one to choose). Instead, you'll make this part of the
WHERE clause in the inner query
(SELECT Id, Field2__c, Field3__c FROM Bs__r WHERE Field3__c > 100)
WHERE Field3__c < 100]
An alternate method
What I've covered so far is for left outer joins in Salesforce, aka parent-child subqueries.
The rules are a bit different if you try to structure your query the other way around, using
B__c as the base object.
The biggest changes are that instead of traversing down the relationship hierarchy (from parent to child), we'll be traversing up the relationship hierarchy (from child to parent). Salesforce allows you to traverse upwards up to 5 levels in the heirarchy (so from a child, to its parent, to its grandparent, to its great-grandparent, to its great-great-grandparent, to its great-great-great-grandparent).
To do so, we prefix the field name on one of the objects up the heirarchy with the full path from the child object to the target object.
[SELECT Id, A_Lookup__c, A_Lookup__r.Field2, A_Lookup__r.Field3 FROM B__c]
Yes, it's that verbose, and yes, you'd need to use the prefix for every field that you wanted to query on
If you had objects A, B, C, and D, were querying object D, and wanted a field on object A, you'd use
You still can't compare a field to another field, but when traversing up a relationship hierarchy, you can include parent fields in the
WHERE clause of the child
[SELECT Id, A_Lookup__c, A_Lookup__r.Field2__c, A_Lookup__r.Field3__c
WHERE Field3__c > 100 AND A_Lookup__r.Field3__c < 100]
some final notes
I was tossing suffixes like
__r around pretty freely, but never really went over them.
__c is the suffix that Salesforce automatically adds to the end of any custom sObject or custom field (on any sObject) that you (or anyone else) adds to Salesforce. I like to think the 'c' stands for 'custom'.
If you're traversing up or down a relationship hierarchy, the
__c suffix becomes
__r (for 'relationship', in my mind) until you finally get to the field that you're looking for (which still ends in
__c if it's a custom field).
Not all fields, nor all objects, have these suffixes in them. In that case, the objects or fields are called 'Standard' (as in, they're automatically provided by Salesforce to everyone).
Account is an example of a standard sObject, and
Id is an example of a standard field.
querying standard fields just uses the field name
[SELECT Id, CreatedDate, OwnerId FROM Account]
OwnerId is an example of a standard relationship field (in most cases it points to a
Querying up a standard relationship, in most cases, requires that you simply drop the 'Id' from the standard relationship field
[SELECT Id, CreatedDate, Owner.Name FROM Account]
Querying down a standard relationship doesn't require
__r, just the relationship name.
[SELECT Id, Name, (SELECT Id, Name FROM Accounts) FROM User]