SQL Subqueries Tutorial: Complete Guide
Learn how to write powerful nested queries with 7 essential subquery techniques, from basic filtering to advanced correlated queries.
Why Subqueries Are Essential for SQL Developers
Subqueries (nested queries) let you solve complex data problems by breaking them into logical steps. Instead of writing convoluted single queries or making multiple database round-trips, subqueries enable you to filter, calculate, and transform data inline.
They are particularly valuable when you need to filter based on aggregated data, check for existence of related records, or perform row-by-row comparisons. Subqueries can appear in SELECT, WHERE, FROM, and HAVING clauses, making them one of SQL's most versatile features.
Understanding when to use subqueries versus JOINs, CTEs, or window functions is key to writing efficient, maintainable SQL. This guide covers 7 essential subquery patterns with practical examples you'll use daily.
Common Mistakes When Working with Subqueries
Using IN with NULL Values
NOT IN returns no results if the subquery contains NULL because NULL comparisons are undefined in SQL. This is one of the most common gotchas. Use NOT EXISTS instead, or explicitly filter out NULLs with WHERE column IS NOT NULL in your subquery.
Overusing Correlated Subqueries
Correlated subqueries execute once per outer row, causing performance issues on large datasets. While powerful for certain scenarios, they can often be replaced with JOINs or window functions for better performance. Always test with realistic data volumes.
Forgetting Subquery Column Aliases
Derived tables (subqueries in FROM clause) require aliases for both the table and its columns in many databases. Omitting these aliases causes syntax errors. Always use AS to name your derived tables and ensure all selected columns have clear names.
Scalar Subquery Returning Multiple Rows
Scalar subqueries in SELECT or WHERE clauses must return exactly one value. If they return multiple rows or columns, you'll get runtime errors. Add LIMIT 1 or use aggregate functions (MAX, MIN, AVG) to ensure single-value results.
Essential SQL Subquery Techniques
These seven subquery patterns will help you handle complex data filtering and transformation scenarios.
Scalar Subqueries in SELECT
Return single values for each row in the main query
Example Query:
SELECT
product_name,
price,
(SELECT AVG(price) FROM products) as avg_price,
price - (SELECT AVG(price) FROM products) as price_difference
FROM products
WHERE category = 'Electronics';When to Use:
Scalar subqueries return a single value and can appear in SELECT clauses to add calculated columns. They're evaluated once per row and are useful for comparisons against aggregated values.
Subqueries in WHERE Clause
Filter rows based on results from another query
Example Query:
SELECT customer_name, total_spent
FROM customers
WHERE total_spent > (
SELECT AVG(total_spent)
FROM customers
)
ORDER BY total_spent DESC;When to Use:
WHERE clause subqueries filter the main query based on conditions evaluated from another dataset. Common for finding records above/below averages or matching specific criteria.
IN and NOT IN Subqueries
Check if values exist in a set of results
Example Query:
-- Find customers who placed orders in 2025
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2025-01-01'
);
-- Find products never ordered
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (
SELECT product_id FROM order_items
);When to Use:
IN subqueries check membership in a result set, while NOT IN finds records that don't match. Great for filtering based on existence checks across tables.
EXISTS and NOT EXISTS
Test for the existence of rows without returning data
Example Query:
-- Find customers with at least one order
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
-- Find products with no inventory
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM inventory i
WHERE i.product_id = p.product_id AND i.quantity > 0
);When to Use:
EXISTS is more efficient than IN for large datasets because it stops searching once a match is found. Use when you only need to verify existence, not retrieve actual values.
Correlated Subqueries
Subqueries that reference columns from the outer query
Example Query:
SELECT
e.employee_id,
e.employee_name,
e.salary,
(SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e.department_id) as dept_avg_salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees e3
WHERE e3.department_id = e.department_id
);When to Use:
Correlated subqueries execute once for each row in the outer query, referencing outer query columns. Powerful for row-by-row comparisons but can be slower on large datasets.
Subqueries in FROM Clause
Use query results as a temporary table (derived table)
Example Query:
SELECT
dept_name,
avg_salary,
employee_count
FROM (
SELECT
d.department_name as dept_name,
AVG(e.salary) as avg_salary,
COUNT(e.employee_id) as employee_count
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
) as department_stats
WHERE employee_count > 5
ORDER BY avg_salary DESC;When to Use:
Derived tables (inline views) let you treat a subquery's results as a table. Useful for multi-step transformations where you need to filter or join on aggregated data.
ALL, ANY, and SOME Operators
Compare values against all or any results in a subquery
Example Query:
-- Find products more expensive than ALL budget items
SELECT product_name, price
FROM products
WHERE price > ALL (
SELECT price
FROM products
WHERE category = 'Budget'
);
-- Find employees earning more than ANY junior employee
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE position = 'Junior'
);When to Use:
ALL requires the condition to be true for every value in the subquery. ANY (synonymous with SOME) requires it to be true for at least one value. Useful for comprehensive comparisons.
Your First Steps with SQL Subqueries
Step 1: Start with Simple WHERE Subqueries
Begin with non-correlated subqueries in WHERE clauses. Practice filtering records based on averages or finding records that match criteria from another table.
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);Step 2: Practice EXISTS for Existence Checks
Learn to use EXISTS for efficient existence checks. Notice how it performs better than IN on large datasets because it stops at the first match.
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);Step 3: Experiment with Derived Tables
Try using subqueries in the FROM clause to create derived tables. This helps break complex queries into readable steps and enables operations on aggregated data.
SELECT category, avg_price
FROM (
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
) as category_averages
WHERE avg_price > 100;Frequently Asked Questions About SQL Subqueries
When should I use a subquery instead of a JOIN?
Use subqueries when you need to filter based on aggregated data or check existence (EXISTS), when the logic is clearer with nested queries, or when you're working with complex conditions. Use JOINs when you need to combine columns from multiple tables or when performance is critical (JOINs are often faster than correlated subqueries).
What is the difference between IN and EXISTS?
IN compares values and returns results if a match is found in the subquery. EXISTS checks if the subquery returns any rows at all. EXISTS is typically faster for large datasets because it stops searching after finding the first match, while IN must evaluate all values. EXISTS also handles NULL values better.
Can I use multiple subqueries in one query?
Yes, you can use multiple subqueries in different clauses (SELECT, WHERE, FROM, HAVING). However, too many subqueries can hurt performance and readability. Consider using CTEs (Common Table Expressions) to organize complex queries with multiple subqueries into more readable, maintainable code.
What are correlated vs non-correlated subqueries?
Non-correlated subqueries are independent and execute once, returning results used by the outer query. Correlated subqueries reference columns from the outer query and execute once for each row in the outer query. Correlated subqueries are more flexible but usually slower.
Why does my NOT IN subquery return no results?
This happens when the subquery contains NULL values. In SQL, comparing anything to NULL (even with NOT IN) results in UNKNOWN, not TRUE or FALSE. To fix this, either use NOT EXISTS or add WHERE column IS NOT NULL to your subquery to filter out NULLs.
How do I optimize slow subqueries?
Several strategies: (1) Replace correlated subqueries with JOINs or window functions, (2) Use EXISTS instead of IN for large result sets, (3) Index columns used in subquery WHERE clauses, (4) Replace scalar subqueries with JOINs when possible, (5) Use CTEs for better query planning, (6) Limit subquery result sets with WHERE conditions.
Can subqueries be used in UPDATE and DELETE statements?
Yes, subqueries work in UPDATE and DELETE. For example: DELETE FROM orders WHERE customer_id IN (SELECT customer_id FROM inactive_customers); or UPDATE employees SET salary = salary * 1.1 WHERE department_id = (SELECT department_id FROM departments WHERE name = "Sales").
What is a derived table and when should I use it?
A derived table is a subquery in the FROM clause that acts as a temporary table. Use it when you need to perform operations on aggregated data, join on the results of a complex query, or break down multi-step transformations into logical chunks. Derived tables make complex queries more readable and can improve performance by pre-filtering data.
Go Deeper with the Complete SQL Crash Course
This guide covers SQL subqueries in detail. The complete book takes you from SQL fundamentals to expert-level techniques with a comprehensive, hands-on approach.
What You'll Get:
- ✓350+ practical SQL examples and exercises covering all skill levels
- ✓140+ SQL interview questions with detailed solutions
- ✓Complete e-commerce database for hands-on practice
- ✓SQL code library with 380+ SQL statements ready to use
- ✓2 bonus SQL cheat sheets: Glossary and Dialect Differences
Trusted by developers worldwide
4.8/5 rating - Bestselling SQL book