For SQL optimization, learn to use window functions and/or Common Table Expressions if available in your variant of SQL. For joins, others have mentioned EXPLAI

For SQL optimization, learn to use window functions and/or Common Table Expressions if available in your variant of SQL. For joins, others have mentioned EXPLAIN: you can use that command to see the "query plan" for any query, which is sort of the equivalent of assembly code. You should learn which of your SQL variant's "join algorithms" at the query plan level are the most performant, and aim to use those types of joins in your queries.