Skip to main content

SQL Subqueries

What are Subqueries?​

SQL Subqueries (also called nested queries or inner queries) are queries placed inside another query. Think of them as a question within a question - you ask one thing first, then use that answer to get your final result.

note

Key Characteristics of Subqueries:

  • Nested Structure: A query inside another query, wrapped in parentheses.

  • Execution Order: Inner query runs first, then outer query uses its results.

  • Versatile Placement: Can appear in SELECT, FROM, WHERE, or HAVING clauses.

  • Return Types: Can return a single value, a row, a column, or a full table.

https://github.com

Animation demonstrating SQL subqueries

success

When to Use Subqueries:

  • Complex Filtering: Find customers who spent more than the average order value
  • Step-by-Step Logic: Break down complicated queries into manageable pieces
  • Dynamic Comparisons: Compare rows against calculated values
  • Data Validation: Check if records exist in other tables
  • Aggregated Filtering: Filter based on grouped calculations

Real-World Example: You want to find all employees earning more than their department's average salary. Instead of calculating each department's average separately, you nest a query that figures out the average, then compare against it.

info

Basic Subquery Syntax​

-- Subquery in WHERE clause (most common)
SELECT column1, column2
FROM table1
WHERE column1 = (SELECT column FROM table2 WHERE condition);
ComponentPurposeExample
Outer QueryMain query that uses subquery resultsSELECT name FROM employees
Inner QueryNested query that executes first(SELECT AVG(salary) FROM employees)
ParenthesesRequired to wrap subqueriesWHERE salary > (subquery)
ComparisonHow outer query uses subquery result=, >, <, IN, EXISTS

Subquery Placement Options​

-- In WHERE clause
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);


-- In SELECT clause (scalar subquery)
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;
-- In FROM clause (derived table)
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_averages;
-- In HAVING clause
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Types of Subqueries​

-- Returns a single value
-- Find products more expensive than average
SELECT 
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    price - (SELECT AVG(price) FROM products) AS price_difference
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

-- Scalar subquery must return exactly one value

Practical Examples​

-- Find products selling above their category average
SELECT 
    p.product_id,
    p.product_name,
    p.category,
    p.price,
    (
        SELECT AVG(price)
        FROM products p2
        WHERE p2.category = p.category
    ) AS category_avg,
    ROUND(
        (p.price - (
            SELECT AVG(price)
            FROM products p2
            WHERE p2.category = p.category
        )) / (
            SELECT AVG(price)
            FROM products p2
            WHERE p2.category = p.category
        ) * 100,
        2
    ) AS percent_above_avg
FROM products p
WHERE p.price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p.category
)
ORDER BY p.category, percent_above_avg DESC;

Subqueries vs JOINs​

tip

When to Choose What:

Use Subqueries When:

  • Logic is clearer with step-by-step thinking
  • You need aggregated values for comparison
  • Checking for existence/non-existence (EXISTS/NOT EXISTS)
  • One-time calculations that don't need repeated access
  • Building derived tables for complex analysis

Use JOINs When:

  • Combining columns from multiple tables
  • Need better performance with large datasets
  • Retrieving data from multiple tables simultaneously
  • Working with well-indexed foreign keys

Example Comparison:

-- Using Subquery
SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    WHERE order_date >= '2024-01-01'
);

-- Using JOIN (often faster)
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01';

Common Subquery Patterns​

info

Useful Patterns You'll Use Often:

  1. Find Records NOT IN Another Table:

    SELECT product_name
    FROM products
    WHERE product_id NOT IN (
        SELECT DISTINCT product_id
        FROM order_items
    );
    -- Finds products never ordered

  2. Compare Against Aggregates:

    SELECT employee_name, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees);
    -- Above-average salaries

  3. Ranked Results:

    SELECT *
    FROM products
    WHERE price = (SELECT MAX(price) FROM products);
    -- Most expensive product

  4. Conditional Aggregation:

    SELECT 
        category,
        COUNT(*) as product_count
    FROM products
    GROUP BY category
    HAVING COUNT(*) > (
        SELECT AVG(cat_count)
        FROM (
            SELECT COUNT(*) as cat_count
            FROM products
            GROUP BY category
        ) AS category_counts
    );
    -- Categories with above-average product counts

Common Mistakes to Avoid​

warning

Watch Out For These:

  1. Forgetting Parentheses: Subqueries must be wrapped

    -- Wrong
    WHERE customer_id IN SELECT customer_id FROM orders;

    -- Correct
    WHERE customer_id IN (SELECT customer_id FROM orders);

  2. Multiple Values When Expecting One:

    -- Will error if subquery returns multiple rows
    WHERE price = (SELECT price FROM products);

    -- Use IN for multiple values
    WHERE price IN (SELECT price FROM products WHERE category = 'Electronics');

  3. NULL Handling with NOT IN:

    -- NOT IN can behave unexpectedly with NULLs
    -- Use NOT EXISTS instead
    WHERE NOT EXISTS (
        SELECT 1 FROM orders WHERE customer_id = customers.id
    );

  4. Performance Blindness:

    -- Don't nest too deep - hard to read and slow
    -- Keep it to 2-3 levels maximum

Best Practices​

  1. Keep It Simple: If it's hard to understand, consider breaking it down
  2. Name Derived Tables: Always alias subqueries in FROM clause
  3. Comment Complex Logic: Future you will thank present you
  4. Test Step by Step: Run inner queries separately first
  5. Consider Alternatives: Sometimes a JOIN or CTE is clearer
  6. Use Appropriate Operators: EXISTS for existence, IN for lists, = for single values

Conclusion​

Subqueries are your tool for asking layered questions - calculate something first, then use that answer to get what you really want. They make complex logic readable by breaking problems into steps. Start with simple subqueries in WHERE clauses, then gradually work up to more complex patterns.

Remember : if your subquery gets too complicated, there's probably a simpler way to write it. Keep it clear, keep it tested, and your queries will be both powerful and maintainable.