JOIN and subqueries both let you combine or filter data using information from more than one table, but they differ in structure -- a JOIN merges rows from multiple tables into one combined result set, while a subquery nests one query inside another, typically to feed a value or list into the outer query.
Key Points: • JOIN is generally more efficient when you need actual columns from both tables in the final output, since the optimizer can plan a single combined access path. • A subquery can appear in the SELECT, WHERE, or FROM clause and is useful when a query's filter condition depends on the result of another query. • Correlated subqueries re-execute for each row of the outer query and can be slower than an equivalent JOIN. • Subqueries with EXISTS or IN are often rewritten by the optimizer into a join-like plan internally, but readability differs from an explicit JOIN.
Example: To list each employee alongside their department name, a JOIN is natural since you need columns from both tables; to simply filter employees who belong to the "HR" department, a subquery against the departments table is often clearer.
Code Example:
-- JOIN: need columns from both tables
SELECT e.name, d.department
FROM employees e
JOIN departments d ON e.department_id = d.id;
-- SUBQUERY: filtering based on another query's result
SELECT name FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');Interview Tip: A concise interview answer is:
"A JOIN combines columns from multiple tables into one result set and is usually the more efficient choice when I need data from both tables. A subquery nests a query inside another, often in the WHERE clause, and I reach for it when I just need to filter based on another table's result rather than actually returning its columns."