I speak "Access" and "Filemaker" so maybe I can translate the terminology a bit as I go.
Filemaker doesn't rely on SQL expressions to define record sources or Record Sets.
Instead, each layout is pointed at a specific table occurrence and data on that layout (think form) can reference fields in other tables based on the relationships defined in Manage | Database | Relationships. That's roughly the same as the "join" part of a SQL expression.
To use your layout to view a specific group of records, you perform a "find". While viewing the layout, you enter find mode, enter search criteria in the fields (including operators and wildcards) and perform the find. This extracts a group of records from the layout's underlying table and this group of records is called the "found set" and is a bit like a form or report's Record Source in Access. Thus performing a find is a bit like the "WHERE" clause in SQL. You can also sort the found set by selecting Sort from the Records menu.
All of the above steps can be performed in a script to automate the process.
What you'll probably find is that simple data searches are much easier to set up in Filemaker, but complex searches may be as complicated or more so than using VB to manipulate a SQL expression.
Hope that gets you started. Feel free to post back with additional questions.
A "5 years later" post with a very similar question. In Access, you were able to run a query which generated a report displaying multiple and specific fields from different but related tables. Is there any way to do such a thing in Filemaker?