In this training we discus how to use temporary sql tables in tableau to perform advanced SQL joins. Natively you cannot create these temporary tables in Tableau using the Custom SQL connector.
Learn More: https://beginwithdata.com

Instead you will need to use the Initial SQL connection to create the table in memory and then reference the queries using the temp tables in the Custom SQL query.

I would love to see if this also works for CTE's (common table expressions) in Tableau. These in memory tables allow you to perform complex transformations and then join them back to your data.