Detailed explanation
A subquery is a SELECT statement nested inside another SQL statement. It supplies a value, a set of values, or a table-shaped result that the surrounding statement uses. Subqueries commonly appear in WHERE, HAVING, SELECT, and FROM clauses, and can also be used within INSERT, UPDATE, or DELETE statements. Their result must fit the context: a scalar comparison requires one column and at most one row, while IN can compare a value against multiple rows. They matter because they express filtering and calculations that depend on other query results.
A non-correlated subquery does not reference columns from the surrounding query, so it can execute independently. For example, SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); finds employees earning above the company-wide average. The inner query produces one aggregate value, which the outer query uses when filtering employees. Saying it “runs once” is a useful conceptual explanation, but it is not a guaranteed physical execution rule: the database optimizer determines the actual execution strategy.
A correlated subquery references a column from an outer query scope. For example, SELECT e.name FROM employees e WHERE e.salary > (SELECT AVG(i.salary) FROM employees i WHERE i.dept_id = e.dept_id); compares each employee’s salary with the average for that employee’s department. The reference to e.dept_id creates the correlation. Conceptually, the database evaluates the inner query using each outer row’s department value. Explicit aliases make that dependency clear and prevent accidental column binding. With ordinary equality, a NULL department ID does not match another NULL department ID.
Interviewers usually expect you to identify this outer reference and explain the performance nuance. Correlated queries can be expensive when repeated inner work scans many rows, but they are not automatically slower than joins. Optimizers may decorrelate them into joins or use other execution strategies; suitable indexes can also reduce lookup work. Compare execution plans rather than relying on syntax alone. For department averages, alternatives include joining employees to grouped department averages or using AVG(salary) OVER (PARTITION BY dept_id), while checking that NULL handling and row counts remain correct.
Key points to remember
- A subquery is a query nested inside another SQL statement.
- A non-correlated subquery has no outer references and can run independently.
- A correlated subquery depends on outer-row values and conceptually executes for each outer row.
- Scalar subqueries return one column and at most one row; IN and EXISTS support multirow results.
- Actual performance depends on the execution plan, data, indexes, and optimizer transformations.
Common mistakes candidates make
- Claiming non-correlated subqueries always execute exactly once or correlated subqueries always execute physically once per row.
- Assuming every correlated subquery is slower than a join without checking execution plans.
- Using ambiguous column references or returning multiple rows where a scalar result is required.
Likely follow-up questions
How does EXISTS work with a correlated subquery?
EXISTS tests whether the inner query returns at least one row, often using an outer key to find matching records. The selected values do not matter, and the database can stop searching once existence is established.
Can the department-average example be rewritten using a join?
Yes: SELECT e.name FROM employees e JOIN (SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id) d ON d.dept_id = e.dept_id WHERE e.salary > d.avg_salary; This expresses the calculation as one average per department joined back to employees, but whether it is faster depends on the execution plan.
What happens if a scalar subquery returns zero rows or multiple rows?
Zero rows produce a NULL scalar value, while multiple rows normally cause an error. An aggregate such as AVG without GROUP BY returns one row even for empty input, with NULL as the average.
Interview tip: Explain independence versus outer-row dependency using the company-average and department-average examples, then briefly qualify the execution and performance claims.