Lesson 8: Subqueries
What are Subqueries?
A subquery (or nested query) is a query inside another SQL query. Subqueries are often used inside WHERE, FROM, or SELECT clauses.
Single-row Subquery
Returns a single value, often used with comparison operators (=, <, >).
SQL
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Multi-row Subquery
Returns multiple values, often used with IN, ANY, ALL.
SQL
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);Correlated Subquery
A subquery that depends on the outer query for values. It is executed once per row of the outer query.
SQL
SELECT e.first_name, e.salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);Practice Quiz
Test your understanding of subqueries: