Understanding SQL Subqueries

A Simple Guide

What are Subqueries?

A subquery is simply a query inside another query — like putting one box inside another box. Let’s understand this with simple examples:

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

Let’s break this down:

1. Inner query SELECT AVG(salary) FROM employees calculates the average salary of all employees
2. Outer query then finds all employees who earn more than this average
3. If average salary is $50,000, it will find all employees earning above $50,000

Here’s another example:

SELECT employee_name,
       salary,
       (SELECT AVG(salary) FROM employees) as company_avg
FROM employees;

How this works:

1. Inner query calculates company average salary once
2. For each employee, shows their name, salary, and the company average
3. You can compare each employee’s salary with the average

You can use subqueries in three ways:

1. In SELECT — to show extra information
2. In WHERE — to filter data
3. In FROM — to treat query results as a table

Nested Subqueries — One Inside Another

Think of nested subqueries like Russian dolls — one query fits inside another, which might fit inside another. Let’s see how:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id IN (
        SELECT location_id 
        FROM locations 
        WHERE country = 'USA'
    )
);

Step by step execution:

1. Innermost query: Finds all location IDs in USA
2. Middle query: Gets all department IDs at these locations
3. Outer query: Finally finds employees working in these departments

Here’s another practical example:

SELECT product_name
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE department_id = 10
);

How this works:

1. Inner query finds all categories in department 10
2. Outer query then finds all products in these categories
3. Result shows product names from specific department

Important things to know:

• Inner query runs first (like opening the smallest doll first)
• Each query can work on its own
• Results flow from inside to outside

Correlated Subqueries — Working Together

A correlated subquery is special because it runs multiple times — once for each row in the outer query. Think of it like this:

SELECT e.employee_name, e.salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Let’s see how this executes step by step:

Get Uma Mahesh Dasari’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

First row: Gets John from Sales department

• Runs inner query to find Sales department average ($60,000)
• Compares John’s salary ($65,000) with $60,000
• John is included because $65,000 > $60,000

Second row: Gets Mary from Marketing department

• Runs inner query again for Marketing’s average ($55,000)
• Compares Mary’s salary ($52,000) with $55,000
• Mary is not included because $52,000 < $55,000

And so on for every employee…

This is why correlated subqueries can be slower:

• If you have 100 employees, inner query runs 100 times
• If you have 1000 employees, it runs 1000 times
• Each row needs its own calculation

Conclusion:

Subqueries are powerful tools in SQL that help you work with complex data problems. Whether you need nested subqueries for step-by-step filtering or correlated subqueries for comparing values, they make your database work easier.