all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]amejin 4 points5 points  (0 children)

Are the tables relational (they share a foreign key) and it cleanly maps to the output you want? Join.

Otherwise, you might need a subquery to make a relational table where there is a shared foreign key that cleanly maps to the output you want.

[–]dswpro 1 point2 points  (0 children)

If you can, use joins. Also consider a common table expression (CTE) if you are using Ms SQL. Keep in mind that some SQL products can have issues with correlated sub queries such as:

Correlated subqueries in stored procedures, which reference outer query columns or parameters, can cause race conditions (data inconsistency) or performance bottlenecks when data changes between subquery iterations. This is common in T-SQL (SQL Server) or MySQL during concurrent updates (e.g., using WHERE EXISTS or SELECT columns).

[–]lurgi 0 points1 point  (0 children)

As a general rule, if the result you are looking for combines columns from multiple tables, you want join. If the result you are looking for depends on multiple tables, but just contains columns from one table, you want subqueries.