} SQL Subqueries — A Beginner-Friendly Guide | IWantToLearnSQL.com

SQL Subqueries — A Beginner-Friendly Guide

⭐ Want to learn SQL step-by-step? Check out our completely FREE Hands-On Practice 👉 SQL course for beginners .



SQL subqueries, also known as nested queries, are queries within another SQL query. They allow you to perform complex data retrieval tasks, filter results, and calculate values dynamically. Learning subqueries is essential for advanced SQL analysis.


📽 Watch the Video Tutorial | See Subqueries in Action!


What is a Subquery?



A subquery is a query nested inside another SQL query, usually inside a SELECT, FROM, or WHERE clause. Subqueries return a value or a set of values that the outer query uses.



Example:



SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE TotalAmount > 500
);


This query returns all customers who have at least one order totaling more than $500.



Types of Subqueries



Subqueries can be categorized based on their placement and return values:



  • Single-row subqueries: Return a single value


  • Multiple-row subqueries: Return multiple values


  • Correlated subqueries: Refer to columns from the outer query


  • Nested subqueries: Subqueries inside other subqueries


1. Single-Row Subquery



Used when the subquery returns only one row and one column.



SELECT FirstName, LastName
FROM Customers
WHERE CustomerID = (
    SELECT CustomerID
    FROM Orders
    WHERE OrderID = 10248
);


How this query works:

  • SELECT FirstName, LastName: Displays the first and last names of the customer.

  • FROM Customers: Retrieves customer records from the Customers table.

  • WHERE CustomerID =: Filters the customers based on a specific CustomerID returned by the subquery.

  • Inner SELECT CustomerID: The subquery retrieves the CustomerID associated with the specified order.

  • WHERE OrderID = 10248: Finds the customer who placed order number 10248.

  • Outer query: Uses the CustomerID returned by the subquery to find that customer's first and last name.


Example: If order 10248 belongs to CustomerID 5, the outer query searches the Customers table for CustomerID 5 and displays that customer's name.



This retrieves the customer who placed a specific order.



2. Multiple-Row Subquery



Used when the subquery returns multiple values. Usually combined with IN.



SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE TotalAmount > 1000
);


How this query works:

  • SELECT FirstName, LastName: Displays the first and last names of customers.

  • FROM Customers: Retrieves records from the Customers table.

  • WHERE CustomerID IN: Checks whether each customer's ID appears in the list of IDs returned by the subquery.

  • Inner SELECT CustomerID: Retrieves the customer IDs associated with qualifying orders.

  • FROM Orders: Searches the Orders table.

  • WHERE TotalAmount > 1000: Selects only orders with a total amount greater than $1,000.

  • IN operator: Allows the outer query to match a CustomerID against multiple values returned by the subquery.


Example: If customers 5, 12, and 20 have orders exceeding $1,000, the outer query returns the names of those three customers.



Returns all customers with at least one order over $1000.


📽 Watch the Video Tutorial | See Subqueries in Action!


3. Correlated Subquery



A correlated subquery depends on the outer query for its values and is evaluated for each row processed by the outer query.



SELECT ProductName
FROM Products p
WHERE Price > (
    SELECT AVG(Price)
    FROM Products
    WHERE CategoryID = p.CategoryID
);


How this query works:

  • SELECT ProductName: Displays the names of products that meet the price condition.

  • FROM Products p: Retrieves products from the Products table and assigns the alias p to the outer query's table.

  • WHERE Price >: Checks whether each product's price is greater than the average price of products in its category.

  • Inner SELECT AVG(Price): Calculates the average product price for a particular category.

  • FROM Products: Uses the Products table to calculate the category's average price.

  • WHERE CategoryID = p.CategoryID: Matches the category in the subquery to the category of the current product being evaluated by the outer query.

  • Correlated reference p.CategoryID: Connects the inner query to the outer query. This is what makes the subquery correlated.


Example: Suppose a category has three products priced at $50, $100, and $150. The average price is $100. The query returns only the product priced at $150 because it is greater than the category average.



This query finds products priced above the average price in their category.



4. Nested Subquery



Subqueries inside other subqueries, useful for multi-step filtering.



SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE TotalAmount = (
        SELECT MAX(TotalAmount)
        FROM Orders
    )
);


How this query works:

  • SELECT FirstName, LastName: Displays the first and last names of the customers who placed the largest order.

  • FROM Customers: Retrieves customer information from the Customers table.

  • WHERE CustomerID IN: Checks whether a customer's ID appears in the list returned by the middle subquery.

  • Middle SELECT CustomerID: Retrieves the customer ID associated with the largest order.

  • FROM Orders: Searches the Orders table for qualifying orders.

  • WHERE TotalAmount =: Finds orders whose total amount matches the maximum order amount returned by the innermost subquery.

  • Innermost SELECT MAX(TotalAmount): Finds the highest order total in the entire Orders table.

  • MAX(TotalAmount): Returns the largest value in the TotalAmount column.

  • Outer query: Uses the customer IDs returned by the middle subquery to retrieve the corresponding customer names.


Example: If the largest order in the database is $5,000 and it belongs to CustomerID 12, the query returns the first and last name of customer 12.



This query uses two levels of subqueries to first identify the largest order amount, then find the customer associated with that order.


▶ IWantToLearnSQL.com
FREE SQL VIDEO TUTORIAL
SQL Subqueries Tutorial
Watch: SQL Subqueries Explained

Learn how to implement SQL Subqueries with practical examples. Follow along as we explain the queries step by step.

▶ WATCH THE FREE VIDEO

Subquery Placement



Subqueries can be used in multiple parts of a query:



  • In SELECT: Calculate a value for each row


  • In FROM: Treat the subquery as a temporary table


  • In WHERE or HAVING: Filter results based on dynamic criteria

NOTE: Subqueries are often used alongside WHERE and HAVING clauses.



Practical Examples



1. Subquery in SELECT



SELECT ProductName,
       (SELECT AVG(Price) FROM Products) AS AvgPrice
FROM Products;


Adds the overall average price for comparison to each product.



2. Subquery in FROM



SELECT Category, MAX(TotalSales)
FROM (
    SELECT Category, SUM(Price) AS TotalSales
    FROM Products
    GROUP BY Category
) AS CategorySales
GROUP BY Category;


This finds the maximum total sales per category.



3. Subquery in HAVING



SELECT Category, SUM(Price) AS TotalSales
FROM Products
GROUP BY Category
HAVING SUM(Price) > (
    SELECT AVG(SUM(Price)) 
    FROM Products
    GROUP BY Category
);


Returns categories with sales above the average category sales.



Common Mistakes with Subqueries



  • • Using subqueries where a JOIN would be more efficient


  • • Forgetting correlated subqueries depend on the outer query


  • • Returning multiple rows in a single-row subquery (causes errors)


  • • Overusing nested subqueries instead of breaking queries into simpler steps


Best Practices



  • • Use JOINs if they improve readability or performance


  • • Alias subquery tables for clarity


  • • Test subqueries independently before combining


  • • Keep subqueries simple and easy to read


  • • Document the purpose of complex subqueries for maintainability


Practice Exercises



  1. Find all customers who placed orders above the average order amount.


  2. List products more expensive than the average price in their category.


  3. Identify the customer with the largest single order.


  4. Show categories where total sales exceed the average total sales.


  5. Use a nested subquery to find products cheaper than all products in another category.


Conclusion



Mastering SQL subqueries is critical for any data professional. Subqueries allow filtering, aggregating, and comparing data in ways that simple queries cannot. Practice regularly, and use subqueries alongside JOINs, WHERE, and HAVING to write efficient and accurate SQL queries.



Continue Learning SQL