Subqueries

Learn how to use nested queries to solve complex data retrieval problems

What are Subqueries?

Queries nested inside other queries

A subquery is a SELECT statement nested inside another SQL statement. Subqueries are used to:

Return data that will be used in the main query's condition
Perform calculations or operations that require multiple steps
Compare values against a set of results

Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Types of Subqueries

Different categories based on their structure and usage

Single-Row Subqueries

Return exactly one row and one column. Used with single-value comparison operators (=, <, >, etc.).

-- Find employees with salary greater than the average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Multiple-Row Subqueries

Return multiple rows. Used with operators like IN, ANY, ALL.

-- Find employees in departments with budget > 400000
SELECT name, department
FROM employees
WHERE department IN (
  SELECT name 
  FROM departments 
  WHERE budget > 400000
);

Correlated Subqueries

Reference columns from the outer query and execute once for each row.

-- Find employees with salary higher than their department average
SELECT name, salary, department
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

The `EXISTS` Operator

Checking for the existence of rows

The `EXISTS` operator is used to test for the existence of any record in a subquery. It returns `TRUE` if the subquery returns one or more records. It is often more efficient than `IN` or `COUNT()` for checking existence.

-- Find departments that have at least one employee
SELECT name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department = d.name
);

Subqueries in Different Clauses

Using subqueries in SELECT, WHERE, and FROM clauses

In SELECT Clause

Used to add calculated values to the result set.

-- Show employee name and department average salary
SELECT name,
       salary,
       (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS dept_avg
FROM employees e1;

In FROM Clause (Derived Tables)

Subqueries in FROM clause create temporary tables for the main query.

-- Departments with above-average budgets
SELECT dept_name, budget
FROM (
  SELECT name AS dept_name, budget
  FROM departments
  WHERE budget > (SELECT AVG(budget) FROM departments)
) AS high_budget_depts;

Previous: Aggregate Functions Next: Set Operators