SQL Subqueries

Videos Discussion

In real world databases, simple SELECT queries are often not enough. Many times, you need to filter, compare, or calculate data based on results of another query.

For example:

Finding employees whose salary is higher than the average salary of their department

Selecting products whose sales exceed the highest sale in the last month

Identifying customers who placed orders but did not make a payment

Writing all of this in a single flat query can become messy. SQL solves this problem with subqueries, also known as nested queries.

A subquery is a query inside another query, used to dynamically supply a value or set of values for the outer query.

How Subqueries Work

The inner query runs first

Its result is passed to the outer query

The outer query uses this result to filter or calculate its final output

Think of it like a question inside a question:

Show me employees whose salary is greater than the average salary of the company
Here, the average salary is calculated first (inner query), and then employees are filtered based on that (outer query).

Types of Subqueries

1. Single-row subquery

Returns only one value

Used with operators like =, >, <

2. Multiple-row subquery

Returns multiple values

Used with operators like IN, ANY, ALL

3. Multiple-column subquery

Returns more than one column

Used in the FROM clause or with IN with tuples

4. Correlated subquery

Inner query depends on the outer query

Executed repeatedly for each row of the outer query

Example - 1: Single Row Subquery

Goal: Find employees earining more than the average salary.

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

Explanation

Inner query calculates average salary

Outer query selects employees earning more than that

Output is filtered automatically

Example - 2: Multiple Row Subquery

Goal: Find employees in departments that have more than 5 employees.

SELECT emp_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5
);

Explanation

Inner query finds departments with >5 employees

Outer query selects employees in those departments

Example - 3: Correlated Subquery

Goal: Find employees whose salary is higher than the average salary of their own department.

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

Explanation

Inner query depends on e1.department_id from outer query

Executed once per employee

Returns employees earning more than their department average

Two Minute Drill

Subquery = query inside a query
Inner query executes first
Types: single-row, multiple-row, multiple-column, correlated
Used for filtering, comparing, or calculating dynamic values
Correlated subqueries run for each row of the outer query