Lesson 23 Advanced Level
23 of 30 lessons

Subqueries

Learn about nested queries and how to use subqueries in WHERE, FROM, and SELECT clauses. Subqueries are powerful tools for complex data analysis.

What is a Subquery?

A subquery is a query nested inside another SQL statement. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.

Basic Subquery Example

SELECT first_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

This returns employees who earn more than the average salary.

Types of Subqueries

  • Single-row subquery: Returns only one row.
  • Multiple-row subquery: Returns multiple rows and uses IN, ANY, or ALL.
  • Correlated subquery: References columns from the outer query.

Multiple-row Subquery Example

SELECT first_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = 1700
);

Correlated Subquery Example

SELECT e1.first_name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

This compares each employee's salary with the average in their department.

Best Practices

  • Use subqueries for filtering, comparison, and calculations.
  • Optimize correlated subqueries to reduce execution time.
  • Use table aliases to avoid ambiguity.

Practice Task