Subqueries (Nested Queries)

Definition: A subquery is a SQL query nested inside a larger query. It is a "query within a query" that provides data to the outer statement. The inner query executes first, and its result is used by the outer query to filter or retrieve data.

Learning Outcome: Subqueries represent the transition from beginner to intermediate SQL. They allow you to perform dynamic filtering—where the criteria for your search isn't a fixed number, but a value that the database has to calculate on the fly.

How It Works

Subqueries are typically enclosed in parentheses. The most common use case is within the WHERE clause to act as a dynamic filter.

Example: Finding Students Above Average Age

If you want to find all students who are older than the class average, you cannot simply guess the average. You must ask SQL to calculate the average first, then use that result to filter the list:

SELECT name
FROM students
WHERE age > (
    SELECT AVG(age)
    FROM students
);

Explanation

• The Inner Query: SELECT AVG(age) FROM students runs first. Let's say it finds the average age is 19.
• The Outer Query: The result (19) is passed to the outer query, which effectively becomes: SELECT name FROM students WHERE age > 19;
• Dynamic Nature: If you add new, older students to the database, the subquery will automatically calculate the new average next time you run it.

Common Subquery Types

Key Notes

• Scalar vs. Multi-row: If your subquery returns a single value (like AVG), you can use = or >. If it returns a list of values, you must use operators like IN.
• Readability: While powerful, subqueries can become hard to read if nested too deeply. For very complex logic, many developers prefer using Joins or CTEs (Common Table Expressions).
• Performance: In some databases, subqueries can be slower than joins because the inner query might run multiple times. Always test your queries if working with millions of rows.