SQL Subqueries Explained: Queries Within Queries
Subqueries (also called nested queries or inner queries) are SQL queries embedded inside another query. They're like functions in programming—you can use the result of one query as input to another.
Think of subqueries as asking a question, then using that answer to ask another question.
Why Use Subqueries?
Subqueries help you solve complex problems by breaking them into smaller, logical steps:
- Find employees earning more than the average salary
- Get customers who have never placed an order
- Calculate the percentage each product contributes to total revenue
You could solve these with JOINs or multiple queries, but subqueries often make the logic clearer.
Types of Subqueries
1. Scalar Subqueries (Single Value)
Returns exactly one value (one row, one column). Can be used anywhere you'd use a single value.
Example: Find employees earning more than the average
How it works:
- Inner query calculates
AVG(salary)→ returns 71000 - Outer query uses that value:
WHERE salary > 71000
2. Column Subqueries (Multiple Rows, One Column)
Returns a list of values. Used with IN, ANY, ALL operators.
Example: Find employees in departments with more than 2 people
Breakdown:
- Subquery finds departments with >2 employees →
('Engineering') - Outer query filters employees in those departments
3. Table Subqueries (Multiple Rows and Columns)
Returns a full result set. Used in the FROM clause as a "derived table".
Example: Calculate percentage of total revenue per product
Note: The subquery in FROM creates a temporary table that the outer query can reference.
Subqueries in Different Clauses
In SELECT (Scalar Subquery)
Add calculated columns based on other data.
In WHERE (Filtering)
Most common use case—filter based on calculated values.
In FROM (Derived Tables)
Treat a query result as a table.
Correlated Subqueries
A correlated subquery references columns from the outer query. It runs once per row of the outer query.
Performance Note: Correlated subqueries can be slow on large datasets because they execute repeatedly. Consider using JOINs or window functions for better performance.
Subqueries vs JOINs
Many problems can be solved with either approach:
Subquery approach:
SELECT name FROM employees
WHERE department IN (
SELECT department FROM departments WHERE location = 'NYC'
);
JOIN approach:
SELECT e.name
FROM employees e
JOIN departments d ON e.department = d.department
WHERE d.location = 'NYC';
When to use which:
- Subqueries: Better for readability when you're filtering or calculating a single value
- JOINs: Better for performance and when you need columns from multiple tables
Common Patterns
Find records NOT in another table
Top N per group
Practice Challenges
Try solving these with subqueries:
- Find products that have never been sold
- Calculate each employee's salary as a percentage of the department total
- Find the second highest salary in the company
- List departments where all employees earn more than $60,000
Key Takeaways
- Subqueries let you break complex logic into readable steps
- Scalar subqueries return one value, used with
=,>,< - Column subqueries return multiple values, used with
IN,ANY,ALL - Table subqueries in
FROMcreate derived tables - Correlated subqueries reference outer query columns (slower but powerful)
- Consider JOINs for better performance on large datasets
Subqueries are a powerful tool in your SQL arsenal. Master them to write more expressive and maintainable queries!