What is an SQL subquery?

An SQL subquery works like an ingredient list for a recipe. Just as you check the ingredients to decide which recipe to follow, a subquery provides data to the main query for it to perform its task. The main query depends on the subquery to determine which rows to select, what values to calculate, or how to filter and join data.

What are SQL subqueries?

A subquery is a query nested inside another SQL query. It acts as a data source for the main query, providing results used for filtering, aggregation, or joining data. Subqueries can appear in various clauses, such as SELECT, FROM, or WHERE.

SQL subqueries can be categorized based on the type of data they return. Here are the main types:

Types of subqueries

  • Scalar subqueries: These return a single value, often used for comparisons or calculations.

  • Multi-row subqueries: These return a list of values, typically used with IN, ANY, or ALL operators.

  • Multi-column subqueries: These return an entire table with multiple rows and columns, which can be used in FROM clauses for further processing.

  • Subqueries in the FROM clause: These subqueries act as temporary tables. They return a set of rows and columns that the main query can treat like a regular table.

  • Subqueries in the WHERE clause: These subqueries are primarily used for filtering data. They provide conditions to the main query, allowing it to select only the rows that meet specific criteria.

Syntax of SQL subqueries

The syntax of a subquery varies based on where it's used, but the basic structure is:

-- Main query
SELECT column1, column2
FROM table1
WHERE column1 = (
    -- Subquery providing data to the main query
    SELECT column3
    FROM table2
    WHERE condition
);

For example, to find employees who earn more than the average salary:

-- Create a new database
CREATE DATABASE CompanyDB;
-- Use the newly created database
USE CompanyDB;
-- Create the employees table
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);
-- Insert employee data
INSERT INTO employees (name, department_id, salary)
VALUES 
    ('Alice', 1, 70000),
    ('Bob', 2, 50000),
    ('Charlie', 1, 80000),
    ('Diana', 3, 60000),
    ('Eve', 2, 45000),
    ('Frank', 3, 55000);
-- Main query: Retrieves names of employees earning above the average salary
SELECT name
FROM employees
WHERE salary > (
    -- Subquery: Calculates the average salary of all employees
    SELECT AVG(salary) 
    FROM employees
);

Try it: Find employees earning less than the average salary.

if you're stuck, please go through the solution below:

Parameter values in subqueries

Subqueries can accept parameter values based on the context. They may return:

  • A single value (scalar subquery), e.g., for comparisons.

  • A list of values, e.g., in the IN clause.

  • A table of rows, used for joins or further processing.

Subqueries to aggregate

Subqueries can be used to aggregate data in stages by nesting calculations.

Example: Calculate departments with a total salary above $50,000.

-- Main query: Retrieves department IDs meeting the salary threshold
SELECT department_id
FROM (
    -- Subquery: Calculates total salary per department
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
) AS department_totals
WHERE total_salary > 50000;

Here, the inner query calculates the total salary per department, and the outer query selects those meeting the condition.

Subqueries in conditional logic

Subqueries are often used in conditional logic for advanced filtering. In addition to filtering data, subqueries can be a powerful tool for making conditional decisions within queries.

Example: Find employees hired after the oldest employee in another department:

-- Main query: Retrieves employees hired after the oldest employee in department 2
SELECT name, hire_date
FROM employees
WHERE hire_date > (
    -- Subquery: Finds the hire date of the oldest employee in department 2
    SELECT MIN(hire_date)
    FROM employees
    WHERE department_id = 2
);

Joining subqueries

Subqueries can serve as temporary tables in a JOIN operation.

Example: List departments with their average salary and number of employees:

-- Main query: Joins department details with aggregated data
SELECT d.department_name, t.avg_salary, t.employee_count
FROM departments d
JOIN (
    -- Subquery: Calculates average salary and employee count per department
    SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
) AS t
ON d.department_id = t.department_id;

Here, the subquery aggregates salary and employee count, and the main query joins it with department data.

Subqueries and UNION

Subqueries can also work with the UNION operator to combine results from multiple queries.

Example: Combine employees from two different departments:

-- Combines employees from departments 1 and 2 without duplicates
SELECT name
FROM (
    -- Subquery 1: Fetches employees from department 1
    SELECT name
    FROM employees
    WHERE department_id = 1
    UNION
    -- Subquery 2: Fetches employees from department 2
    SELECT name
    FROM employees
    WHERE department_id = 2
) AS combined_employees;

In this case, two subqueries fetch employees from separate departments, and UNION combines the results without duplicates.

Key takeaways

  • Subqueries are nested SQL queries used for advanced data filtering, aggregation, and joining.

  • They can return single values, lists, or entire tables for further processing.

  • Subqueries simplify complex queries, allowing us to break tasks into manageable steps.

  • They work well with conditional logic, joins, and operators like IN, ANY, ALL, and UNION.

Ready to learn SQL and become a database expert? Join our Learn SQL course and follow the path to becoming a Database Professional with SQL! Unlock your potential and gain the skills employers seek. Start your journey today!

Frequently asked questions

Haven’t found what you were looking for? Contact Us

What is the difference between a query and a subquery?

A query is a standalone SQL command used to retrieve, modify, or manipulate data from a database. A subquery, on the other hand, is a query embedded within another query, typically used to provide intermediate results that the main query relies on.

How are subqueries different from SELECT statements?

A subquery is a specialized SELECT statement nested within another SQL command, such as SELECT, INSERT, UPDATE, or DELETE. The difference lies in its use:

  • A standalone SELECT fetches data independently.
  • A subquery feeds data into another query to filter, calculate, or aggregate results.

What are the different types of subqueries?

Subqueries can be categorized into:

  • Single-row subqueries: Return a single value, often used with comparison operators like =, <, or >.
  • Multi-row subqueries: Return multiple values, commonly used with operators like IN or ANY.
  • Correlated subqueries: Depend on the outer query for execution.
  • Nested subqueries: Contain multiple levels of subqueries within each other.

When should you use a subquery?

Use a subquery when:

  • You need to filter data based on intermediate calculations or results.
  • Performing operations in multiple stages simplifies complex logic.
  • Temporary tables or views would be too cumbersome.
  • You want to create dynamic, context-specific filtering or joining.

Can a subquery return more than one value?

Yes, subqueries can return multiple values (e.g., for use in the IN clause) or even entire tables (for use in the FROM clause). However, in contexts like WHERE salary > (...), the subquery must return a single value.

What happens if a subquery returns no results?

If a subquery returns no results, the condition it’s used in might evaluate to FALSE, depending on the context. For example, in a WHERE clause, it may exclude all rows if no match is found.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved