Welcome to SQL subqueries, an essential tool for budding SQL programmers and data analysts. A subquery is “a query within a query, a way to perform multi-layered data investigations.” This guide walks through what subqueries are and why they matter, the different subquery types, real-world examples, and best practices for writing clean, efficient subqueries.

If you’ve already learned the basics of SELECT, WHERE, and JOIN, SQL subqueries are the next skill that unlocks a whole new category of questions you can ask your data — questions that depend on the results of another query, like “which employees earn more than their department’s average salary?” or “which customers have never placed an order?” Instead of pulling raw data into a spreadsheet and calculating the answer by hand, a well-written subquery lets the database do that work for you in a single statement.

Whether you’re a beginner web designer branching into data analysis, an Excel user ready to graduate to a real query language, or a data analyst looking to write cleaner SQL, mastering SQL subqueries will make you noticeably more effective at answering real business questions with data. By the end of this guide, you’ll be comfortable writing non-correlated and correlated subqueries, using EXISTS and IN, building derived tables, and knowing when a subquery is the better choice over a JOIN.

What Exactly Are SQL Subqueries? The Building Blocks Explained

Defining a Subquery (Nested Query or Inner Query)

An SQL subquery, also called a nested query or inner query, is a SELECT statement embedded within another SQL statement. The outer statement executes the subquery first, then uses its result.

The basic syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator (
    SELECT column_name(s)
    FROM table_name
    WHERE condition
);

Why Use SQL Subqueries and All Their Benefits?

Subqueries offer several advantages:

  • Breaking Down Complex Problems: Enable structured, step-by-step approaches to intricate data retrieval
  • Performing Multi-Step Data Retrieval: Allow chaining operations where one query’s result feeds another
  • Filtering Data Dynamically: Generate filtering values based on aggregated data
  • Improving Readability: Can clarify logic compared to complex JOINs or temporary tables
  • Checking for Existence: Verify related records without retrieving their data

Getting Started: Your First SQL Subqueries

Subqueries in the WHERE Clause

WHERE clause subqueries filter main query results based on inner query outcomes.

Example 1: Finding employees in a specific department

Given tables: Employees (EmployeeID, Name, Salary, DepartmentID) and Departments (DepartmentID, DepartmentName).

SELECT Name, Salary
FROM Employees
WHERE DepartmentID = (
    SELECT DepartmentID
    FROM Departments
    WHERE DepartmentName = 'Sales'
);

The subquery returns the Sales department ID, which the outer query uses to filter employees.

Example 2: Finding products above average price

Products table: ProductID, ProductName, Price.

SELECT ProductName, Price
FROM Products
WHERE Price > (
    SELECT AVG(Price)
    FROM Products
);

Using IN and NOT IN with Subqueries

Subqueries returning multiple values pair with the IN and NOT IN operators.

Example 1: Finding customers who placed orders

Tables: Customers (CustomerID, CustomerName) and Orders (OrderID, CustomerID, OrderDate).

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
    SELECT DISTINCT CustomerID
    FROM Orders
);

Example 2: Finding customers without orders

SELECT CustomerName
FROM Customers
WHERE CustomerID NOT IN (
    SELECT DISTINCT CustomerID
    FROM Orders
);

Types of SQL Subqueries: Understanding the Differences

Non-Correlated (Simple) Subqueries

Non-correlated subqueries execute once, independently:

  • Execution: Inner query runs once; result feeds the outer query
  • Independence: Inner query runs standalone without outer query values
  • Simplicity: Easier to understand and debug

The previous examples (Sales employees, above-average products, order-placing customers) all demonstrate non-correlated subqueries.

Correlated Subqueries: The Interdependent Duo

Correlated subqueries depend on outer query values:

  • Execution: Inner query runs repeatedly for each outer row
  • Dependence: Inner query references current outer row values via aliases
  • Complexity: More complex, with potential performance concerns from repeated execution

Example: Finding employees earning above their department’s average

Employees table: EmployeeID, Name, Salary, DepartmentID.

SELECT e1.Name, e1.Salary, e1.DepartmentID
FROM Employees e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentID = e1.DepartmentID
);

Process:

  1. The outer query processes each Employees row (aliased e1)
  2. For each row, the inner query calculates that department’s average salary
  3. The outer query compares the individual salary against the department average

Using EXISTS and NOT EXISTS with Correlated Subqueries

EXISTS checks if a subquery returns any rows; NOT EXISTS does the opposite.

Example: Finding departments with employees

Tables: Departments (DepartmentID, DepartmentName) and Employees (EmployeeID, Name, Salary, DepartmentID).

SELECT d.DepartmentName
FROM Departments d
WHERE EXISTS (
    SELECT 1
    FROM Employees e
    WHERE e.DepartmentID = d.DepartmentID
);

For each department, the subquery checks for matching employees. EXISTS returns TRUE if any employee is found, FALSE otherwise. The SELECT 1 value is convention; EXISTS only cares about row existence.

Advanced SQL Subquery Techniques

Subqueries in the SELECT Clause (Scalar Subqueries)

SELECT clause subqueries, called scalar subqueries, must return exactly one value (one row, one column). Errors occur with multiple rows or columns, and these often correlate to the outer query.

Example: Displaying products with total quantities sold

Tables: Products (ProductID, ProductName, Price) and OrderDetails (OrderDetailID, OrderID, ProductID, Quantity).

SELECT
    p.ProductName,
    p.Price,
    (SELECT SUM(od.Quantity)
        FROM OrderDetails od
        WHERE od.ProductID = p.ProductID
    ) AS TotalQuantitySold
FROM Products p;

For each product, the correlated scalar subquery calculates the total quantity sold for that specific product. Scalar subqueries in SELECT lists may impact performance since they execute for every returned row, so always test performance.

Subqueries in the FROM Clause (Derived Tables)

FROM clause subqueries create temporary tables called derived tables or inline views. Derived tables require aliases.

Example: Calculating average order value per customer

Tables: Orders (OrderID, CustomerID, OrderTotal) and Customers (CustomerID, CustomerName).

SELECT
    c.CustomerName,
    AvgOrders.AverageOrderValue
FROM Customers c
JOIN (
    SELECT
        o.CustomerID,
        AVG(o.OrderTotal) AS AverageOrderValue
    FROM Orders o
    GROUP BY o.CustomerID
) AS AvgOrders ON c.CustomerID = AvgOrders.CustomerID;

Process:

  1. The subquery executes first, creating a temporary table (AvgOrders) with CustomerID and average order values
  2. The outer query joins Customers with AvgOrders to display customer names and their average order values

Derived tables break complex calculations into manageable steps or create intermediate datasets for further querying.

SQL Subqueries vs. JOINs: When to Use Which?

Readability:

  • Subqueries (especially non-correlated) may read intuitively as step-by-step processes
  • Very complex nested subqueries become harder to follow than well-structured JOINs

Performance:

  • JOINs generally outperform subqueries; database optimizers excel at JOIN optimization
  • Correlated subqueries can be inefficient due to repeated execution per row
  • Rewriting correlated subqueries as JOINs (sometimes with outer joins and WHERE filters, or window functions) improves performance

Specific Use Cases for Subqueries:

  • Aggregates in Conditions: Comparing values to aggregates (AVG, SUM, COUNT) is most straightforward with subqueries
  • EXISTS, NOT EXISTS, IN, NOT IN: Powerful subquery constructs, especially correlated ones, for checking existence without data retrieval
  • Scalar Subqueries: Fetching single computed values per row in SELECT lists
  • Derived Tables: Performing operations on intermediate result sets

General Guideline: If JOINs solve the problem cleanly and efficiently, they’re often preferred. However, subqueries are valuable when they provide clearer logical solutions or are necessary for operations like EXISTS or comparisons with aggregated values.

Best Practices for Writing Efficient SQL Subqueries

  • Keep Subqueries Simple: Break down overly complex subqueries or explore Common Table Expressions (CTEs) or temporary tables
  • Prefer JOINs for Equivalent Logic (Often): Choose JOINs when they achieve the same results more performantly or readably
  • Use EXISTS Instead of IN with Large Subquery Results: EXISTS is more efficient for correlated existence checking; it stops processing upon finding one matching row, while IN generates its full result set
  • Select Only Necessary Columns: Include only the columns needed by the outer query; avoid SELECT * unless necessary
  • Filter Early: Apply WHERE clauses within subqueries to reduce result sets before outer query processing
  • Test Performance: Always test subquery performance, especially correlated ones on large datasets, using your database’s EXPLAIN feature
  • Use Aliases: Employ table aliases to improve readability and explicitly reference tables, especially in correlated subqueries
  • Consider Alternatives for Complex Cases: CTEs often provide better readability and modularity than deeply nested subqueries

In Conclusion

This guide covered the journey from fundamental subquery definitions through various types—non-correlated and correlated—and their applications in WHERE, SELECT, and FROM clauses. Subqueries enable solving complex data retrieval problems, dynamic filtering, and calculations that are difficult with simpler SQL statements.

To recap the key takeaways: use non-correlated subqueries when the inner query can run independently, and correlated subqueries when each outer row needs its own calculation, such as comparing a value against its own group’s average. Reach for EXISTS/NOT EXISTS when you only need to check whether related rows exist, use scalar subqueries in a SELECT list to attach a single computed value per row, and use derived tables in the FROM clause when you need to treat an intermediate result set as its own table. And when performance matters, remember that JOINs are often faster than correlated subqueries for equivalent logic, so it’s always worth testing both approaches.

Try to re-frame data questions you encounter as problems that might be solved with a subquery. Experiment with the examples provided, adapt them to your own datasets, and don’t be afraid to test different approaches, comparing subqueries with JOINs to see what works best.

Consistent application of these techniques and adherence to best practices will make SQL subqueries an intuitive and powerful tool for deeper data insights, and one you’ll reach for confidently the next time raw data alone isn’t enough to answer the question in front of you.