FeatureJoiner, FeatureMerger and InlineQuerier are likely the most common Transformers for this:

• FeatureJoiner requires common JOIN attributes in both sources, but will output 1-to-Many and Many-to-Many. It can be used in a "conditionless" mode by generated a Dummy join attribute on both input tables of equal value like "1" and joining on the dummy attribute.
• FeatureMerger by default uses common JOIN attributes in both sources, but can alternately be used in a "conditionless" mode as described here: https://knowledge.safe.com/questions/70896/join-two-tables-without-conditions.html. By default it doesn't output directly 1-to-Many results, but can generate 1-to-Many lists that can be exploded with ListExploder, and in "conditionless" mode Many-to-Many lists.
• InlineQuerier is the most flexible and can do just about any type of joining you can think of and apply filters or not at the same time, but requires the relevant SQL expression as an input

@charry The article Merging or Joining Spreadsheet or Database Data has a nice chart that tries to illustrate the best transformer for a specific task