Two Tools For One Job
A subquery is a query nested inside another query. A join combines tables side by side. Often both can answer the same question, so the choice is about clarity and performance.
Subquery Style
A subquery is great when you only need a scalar value or a set to test against:
- A scalar subquery returns one value, like the average price.
- An IN subquery returns a list to check membership against.
- An EXISTS subquery checks whether matching rows exist.
Join Style
A join is usually better when you need columns from both tables in the output. Pulling fields from the related table requires a join, since a subquery in the WHERE clause cannot expose those columns to SELECT.
Performance Notes
Modern optimizers often rewrite an IN subquery into a join internally, so they perform similarly. However, a poorly written subquery that runs per row can be slow. Prefer a join when you need related columns, and a subquery when you only need a filter.
Key idea
Use a join when you need columns from both tables and a subquery when you only need a value or membership test, since optimizers often treat them alike.