Read this SQL tutorial to learn when to use SELECT, JOIN, subselects and UNION to access multiple tables with a single statement.

SQL Server 2022 takes that ability a few steps further. Last year, I wrote a column on the introduction of query store hints into Azure SQL Database.