SQL Subqueries - Complete Guide with Real Examples

Published: May 29, 2026

Subqueries are one of SQL's most powerful features, allowing you to nest queries within queries. They're essential for solving complex data problems and can make your SQL code more modular and easier to understand.

What is a Subquery?

A subquery (also called an inner query or nested query) is a query within another query. Subqueries are used to return data that will be used by the main query. They can appear in various clauses: WHERE, FROM, SELECT, or HAVING.

Types of Subqueries

1. Scalar Subqueries

Return a single value (one row, one column) and can be used anywhere a single value is expected.

SELECT 
    customer_id,
    order_id,
    amount,
    (SELECT AVG(amount) FROM orders) AS avg_order_amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

2. Row Subqueries

Return a single row with multiple columns.

SELECT * FROM employees
WHERE (department_id, salary) = 
    (SELECT department_id, MAX(salary)
     FROM employees
     GROUP BY department_id);

3. Table Subqueries (Inline Views)

Return multiple rows and columns, used in the FROM clause.

SELECT department, avg_salary
FROM (
    SELECT 
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) department_summary
WHERE avg_salary > 50000;

Subquery Placement

WHERE Clause Subquery

Filter main query results based on subquery results.

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN 
    (SELECT customer_id FROM orders WHERE amount > 1000);

FROM Clause Subquery (Inline View)

Use subquery results as a temporary table.

SELECT ranked_customers.customer_id, ranked_customers.rank
FROM (
    SELECT 
        customer_id,
        ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
    FROM orders
) ranked_customers
WHERE ranked_customers.rank <= 10;

SELECT Clause Subquery

Add computed columns based on subqueries.

SELECT 
    customer_id,
    customer_name,
    (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

Correlated Subqueries

A correlated subquery references columns from the outer query. It executes once for each row processed by the outer query.

SELECT 
    e1.employee_name,
    e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

This query finds employees whose salary is above the average for their department.

Subquery Operators

IN - Check if value exists in subquery results
NOT IN - Check if value doesn't exist in subquery results
EXISTS - Check if subquery returns any rows
NOT EXISTS - Check if subquery returns no rows
ANY - Compare value to any value in subquery results
ALL - Compare value to all values in subquery results

EXISTS vs IN

Both check for row existence, but EXISTS is often faster:

-- Using IN (checks all values)
SELECT customer_id FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Using EXISTS (stops at first match)
SELECT customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Subquery vs JOIN

Often, subqueries can be replaced with JOINs for better performance:

-- Subquery approach
SELECT customer_id FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- JOIN approach (usually faster)
SELECT DISTINCT c.customer_id FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Visualize Your Subqueries

Complex subqueries with multiple nesting levels can be hard to understand. SQL Visualizer shows you the data flow and relationships between your subqueries and main query.

Understand your subquery structure visually!

Visualize Subqueries Now →

Subquery Best Practices

Use meaningful aliases for subqueries
Prefer JOINs when they're more efficient
Avoid excessive nesting (limit to 3-4 levels)
Use EXISTS instead of IN for large result sets
Consider CTEs for complex nested queries
Index columns used in subquery joins

Common Subquery Mistakes

Forgetting to alias the subquery in FROM clause
Using = instead of IN when subquery returns multiple rows
Not understanding the difference between EXISTS and IN
Over-using correlated subqueries (can be slow)

Conclusion

Subqueries are a crucial SQL skill. Whether you're filtering data, creating temporary views, or performing complex calculations, mastering subqueries will make you a better SQL developer. Start with simple subqueries and gradually work your way to more complex nested queries!