SQL Subqueries

A subquery (also known as an inner query or nested query) is an SQL query nested inside a larger outer query—such as a SELECT, INSERT, UPDATE, or DELETE statement. Subqueries allow you to execute dynamic, multi-step queries where the output of one query serves as an input condition or data source for another.

Basic Structure & Execution Order

A subquery is always enclosed in parentheses () and typically runs before the outer query executes, passing its result directly to the outer query.

-- Outer Query
SELECT column_name
FROM table_name
WHERE column_name OPERATOR (
    -- Inner Subquery
    SELECT column_name
    FROM table_name
    WHERE condition
);

+-------------------------------------------------------------+
|                        OUTER QUERY                          |
|  SELECT EmployeeName, Salary FROM Employees WHERE Salary >  |
|                                                             |
|          +---------------------------------------+          |
|          |            INNER SUBQUERY             |          |
|          |     (SELECT AVG(Salary) FROM Employees)|          |
|          +---------------------------------------+          |
|                              |                              |
|                              v                              |
|                       Evaluates to 60000                    |
+-------------------------------------------------------------+

Types of Subqueries

Subqueries fall into three main categories based on the data shape they return:

1. Single-Value (Scalar) Subquery

Returns a single value (one row, one column). You use standard comparison operators (=, >, <, >=, <=, <>) with scalar subqueries.

Example: Find all employees earning more than the average salary.

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > (
    SELECT AVG(Salary) 
    FROM Employees
);

2. Multi-Value Subquery

Returns multiple rows in a single column. These are paired with set operators like IN, NOT IN, ANY, or ALL.

Example: Retrieve all customers who placed an order in the year 2026.

SELECT CustomerName, City, Country
FROM Customers
WHERE CustomerID IN (
    SELECT DISTINCT CustomerID
    FROM Orders
    WHERE OrderDate >= '2026-01-01'
);

3. Multi-Column Subquery (Table Subquery / Derived Table)

Returns a result set with multiple rows and columns. When used in the FROM clause, the subquery acts as a temporary inline table and must be given an alias.

Example: Find the maximum average salary across all departments.

SELECT MAX(DeptAvg.AvgSalary) AS HighestDeptAvg
FROM (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
) AS DeptAvg;

Subquery Placement Options

Subqueries can be placed in different clauses depending on what you want to achieve:

Example: Subquery in the SELECT Clause

SELECT 
    EmployeeID, 
    FirstName, 
    Salary,
    (SELECT AVG(Salary) FROM Employees) AS CompanyAverage,
    Salary - (SELECT AVG(Salary) FROM Employees) AS DifferenceFromAvg
FROM Employees;

Correlated Subqueries vs. Standard Subqueries

Understanding how a subquery executes relative to the outer query is critical for query performance:

• Non-Correlated Subquery (Standard): The inner subquery runs once independently. Its result is passed back to the outer query.
• Correlated Subquery: The inner subquery references a column from the outer query table. It executes once for every single row evaluated by the outer query.

Correlated Subquery Example

Find employees whose salary is higher than the average salary of their specific department:

SELECT e.EmployeeID, e.FirstName, e.DepartmentID, e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(d.Salary)
    FROM Employees d
    WHERE d.DepartmentID = e.DepartmentID -- References outer query table 'e'
);

Subquery vs. JOIN: Which Should You Use?

Many tasks solved with subqueries can also be implemented using JOIN statements.

-- Subquery approach
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

-- Equivalent JOIN approach
SELECT DISTINCT c.CustomerName
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

Key Differences

• Readability: Subqueries often express logical intents (e.g., "Find items where X exists in Y") more naturally than complex joins.
• Performance: Relational database query optimizers can often rewrite simple subqueries into joins automatically. However, correlated subqueries and deep nesting can degrade performance on large datasets. Use JOIN when combining multiple full tables or when performance is critical.