Two Ways To Combine Data
A subquery is a query nested inside another. A join combines tables side by side. They often answer the same question, so the choice is about clarity and how the planner can optimize.
How They Differ
- A join produces a flat result and can return columns from both tables at once.
- A subquery in WHERE acts as a filter and returns no columns from the inner table.
- A join may multiply rows when the other side has several matches, which a filtering subquery does not.
Choosing Between Them
- Use a join when you need columns from both tables in the output.
- Use a subquery when you only need to test membership or compute a value to compare against.
- Many engines rewrite a simple IN subquery into a semi join internally, so performance is often similar, but the readable form should win.
A Subtle Bug
An IN subquery that returns a NULL can make NOT IN drop every row, because a comparison with NULL is unknown. Prefer NOT EXISTS when nulls are possible.
Key idea
Joins return columns from both sides and can multiply rows, while filtering subqueries only test rows, so pick by what the output needs and watch NULLs in NOT IN.