Querying Subsets with Subqueries

Querying Subsets with Subqueries

In this lesson, you will learn how to use nested queries to compute a specific "target value" that the main query needs to make a decision.

The Power of Intermediate Calculations

Sometimes, you need to filter your data based on a value that isn't sitting in a table, but rather something you need to calculate first. Imagine you want to find all employees who earn more than the average salary of the entire company.

You can't write WHERE salary > AVG(salary) because SQL filters rows one by one, and AVG() is an aggregate function that requires looking at the whole group at once. Instead, you need a two-step process:

1. The Subquery: Calculate the company's average salary.
2. The Main Query: Compare each individual's salary against that result.

Seeing it in Action

Think of the subquery as a "helper" that runs once, hands a value to the main query, and then steps aside.

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

Here is what happens behind the scenes:

• The database engine first executes the query inside the parentheses: SELECT AVG(salary) FROM employees. Let's say this returns 50000.
• The main query then effectively becomes: SELECT employee_name, salary FROM employees WHERE salary > 50000.
• The final results are returned to you.

This approach is incredibly powerful because it keeps your logic clean. You don't have to manually calculate the average or store it in a temporary variable; the database handles the intermediate calculation for you automatically. You can use these subqueries anywhere a single value is expected, such as in WHERE clauses or even within your SELECT list to compare an individual row to a group average.

Key Takeaway: Use a subquery when you need to calculate an intermediate value, such as an average or a count, to act as a benchmark for filtering your main result set.