✏️ Explanatory Question
Level: Intermediate — Subqueries are the foundation of complex, nested SQL logic.
A subquery (or inner/nested query) is a SELECT query placed inside another SQL statement. The inner query runs first, and its result is used by the outer query. Subqueries can appear in the SELECT, FROM, WHERE, or HAVING clause.
=, it must return a single value; with IN, it can return multiple values.
| Type | Returns | Common Operator |
|---|---|---|
| Scalar | One value | =, >, < |
| Column | One column, many rows | IN, ANY, ALL |
| Row | One row, many columns | = (with row constructor) |
| Table | Full result set | FROM (derived table) |
-- Scalar subquery: employees earning above the average
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Column subquery: employees in Kolkata departments (IN)
SELECT name FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Kolkata');
-- Table subquery (derived table) in FROM
SELECT dept_id, avg_sal
FROM (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
) AS dept_avg
WHERE avg_sal > 50000;
-- Subquery in SELECT: show each employee's dept name
SELECT name,
(SELECT dept_name FROM departments d
WHERE d.dept_id = e.dept_id) AS department
FROM employees e;