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
📽 Watch Video!
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
);
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
);
Returns all customers with at least one order over $1000.
3. Correlated Subquery
A correlated subquery depends on the outer query for its values and is executed row by row.
SELECT ProductName
FROM Products p
WHERE Price > (
SELECT AVG(Price)
FROM Products
WHERE CategoryID = p.CategoryID
);
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
)
);
Finds customers with the largest order in the database.
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
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
- Find all customers who placed orders above the average order amount.
- List products more expensive than the average price in their category.
- Identify the customer with the largest single order.
- Show categories where total sales exceed the average total sales.
- 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.
⭐ Need an SQL Certificate, CEUs, or a FREE start with Hands-On Practice! → Get Started Here!