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
Test Your Skills with Real Interview Questions
Ready to tackle real-world subquery problems from top tech companies? Try these:
- Second Highest Salary (Airbnb) - Classic subquery problem testing MAX() with nested queries
- Page With No Likes (Meta) - Use NOT IN or NOT EXISTS to find missing relationships
- Top 3 Department Salaries (Amazon) - Correlated subquery for ranking within groups
- Page Recommendations (Snowflake) - Combine subqueries with JOINs for social graph queries
These questions will test your mastery of scalar, column, and correlated subqueries in production scenarios.
Related SQL Challenges
If you want shorter challenge pages focused on the same nested-query patterns, try:
- Second Highest Salary - Compact MAX-with-subquery logic around distinct value tiers.
- Products Never Ordered - Anti-join and NOT IN practice for “missing related rows” questions.
- Customers Who Bought All Products - A clean relational-division pattern using counts or NOT EXISTS.
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!
Tool Workflow
Use tools when subqueries make the SQL more expressive but harder to inspect
Nested queries help structure logic, but once they stack up it helps to explain the final statement, compare alternatives, and review the broader query workflow.
Related Articles
- Mastering CTEs: Writing Cleaner, Better SQL for the named-step alternative when nested subqueries start to hurt readability.
- Mastering SQL Set Operations: UNION, INTERSECT, and EXCEPT for row-comparison patterns that sometimes replace nested filtering logic.
- SQL CASE Statements Explained for the branching logic that often appears inside subqueries and derived tables.