When inputting the optional SQL clause, ArcGIS automatically adds quotation marks "" to the field names in the dialog box. This will pass the tool's error checking successfully but will cause the tool to fail with an error.
If you verify the SQL clause in the dialog box, it will give a SQL error with no specifics. When adding the clause, remember to remove the quotation marks.
e.g. If you want to join Layer1 to Layer 2 on common field ID and where Layer 1 contains "Cape Town", ArcGIS will format your expression in the following way: