Q11: What are the types of subqueries (Scalar, Multi-row, Correlated)?
Difficulty:MediumTopic:Subqueries
A subquery is an inner query nested within an outer SELECT, INSERT, UPDATE, or DELETE statement.
- Scalar Subquery: Returns a single value (1 row, 1 column). Can be used anywhere expressions are allowed.
- Multi-Row Subquery: Returns multiple rows in a single column. Used with operators like
IN,ANY, orALL. - Correlated Subquery: References columns from the outer query. The subquery executes iteratively once for every row processed by the outer query, which can impair performance on large datasets.
Correlated Subquery Example
-- Find employees earning more than their department average
SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(sub.salary)
FROM employees sub
WHERE sub.department_id = e.department_id -- Correlation link
);💡 The inner query re-calculates average department salary for each candidate row of the outer employee loop.
Key Takeaways
- Correlated subqueries evaluate repeatedly for each outer candidate row.
- Convert correlated subqueries to explicit JOINs or CTEs with window functions to optimize query performance.