Subqueries in SELECT, FROM, and WHERE

1. The Foundation: Subqueries in the FROM Clause

The most intuitive place to start with subqueries is in the FROM clause. A subquery in the FROM clause is often called a derived table. The logic is simple:

1. The database runs the inner query (the subquery) first.
2. The result set of this inner query is treated as a temporary, virtual table.
3. The outer query then runs, using this virtual table as its source.

This pattern is essential when you need to perform aggregations in multiple stages. For instance, you can't write AVG(SUM(sales)) in a single query. You must first calculate the SUM in a subquery and then calculate the AVG of those sums in the outer query.

To see the basic structure and a practical example, let's turn to a helpful article from ThoughtSpot.

As you saw, a good practice is to write and test your inner query first to ensure it produces the intermediate result you expect. Then, you can wrap the outer query around it.

Let's look at a common analytical scenario: finding the average number of sales per day.

-- Step 1: Find the number of sales for each day (this is the inner query)
SELECT
    sale_date,
    COUNT(order_id) AS sales_count
FROM sales
GROUP BY sale_date;

-- Step 2: Use the above as a derived table to find the average
SELECT
    AVG(daily_sales.sales_count) AS average_daily_sales
FROM
    (
        SELECT
            sale_date,
            COUNT(order_id) AS sales_count
        FROM sales
        GROUP BY sale_date
    ) AS daily_sales; -- The alias 'daily_sales' is mandatory

This two-step logic is impossible to execute in a single aggregation but becomes straightforward with a subquery.

2. Dynamic Filtering: Subqueries in the WHERE Clause

Subqueries are also incredibly powerful when used in the WHERE clause to create dynamic filters. Instead of comparing a column to a fixed value (e.g., WHERE price > 100), you can compare it to a value or set of values returned by another query.

There are two main categories:

1. Subqueries returning a single value: The inner query returns one row and one column. You can use standard comparison operators like =, >, <, etc.

   • Use case: "Find all products with a price higher than the average price."

2. Subqueries returning multiple values: The inner query returns a list of values (one column, multiple rows). You must use operators designed for lists, such as IN, NOT IN, ANY, or ALL.

   • Use case: "Find all employees who work in the 'Sales' or 'Marketing' departments."

The following resource provides examples for both types.

Let's illustrate with a clear example. Imagine we have employees and departments tables.

Example 1: Single-Value Subquery
Find employees who earn more than the company's average salary.

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

The inner query (SELECT AVG(salary) FROM employees) runs first, returns a single number (e.g., 60000), and the outer query becomes WHERE salary > 60000.

Example 2: Multi-Value Subquery
Find all employees who work in a department located in 'New York'.

SELECT
    employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

The inner query (SELECT department_id FROM departments ...) could return a list of department IDs (e.g., 3, 7). The outer query then finds all employees whose department_id is in that list.

3. On-the-Fly Calculations: Subqueries in the SELECT Clause

Finally, you can place a subquery in the SELECT clause to calculate a new column. This type of subquery is often called a scalar subquery because it must return a single value (one row, one column) for each row of the outer query's result set.

Scalar subqueries are often correlated, meaning the inner query's calculation depends on the current row being processed by the outer query.

Let's explore this with an article that classifies subquery types well.

Example: Calculating an average for each product
Let's find each product and compare its price to the average price of all products in its category.

SELECT
    p.product_name,
    p.price,
    p.category,
    (SELECT AVG(price) FROM products WHERE category = p.category) AS avg_category_price
FROM
    products AS p;

For each product p in the outer query, the inner subquery runs and calculates the average price for that specific product's category.

The image below shows a real-world example combining subqueries in both the SELECT and FROM clauses.

Let's break down the query in the image:

1. FROM Clause Subquery: (select top 10 Id, DisplayName from dbo.Users order by Reputation desc) runs first. It finds the 10 users with the highest reputation and creates a derived table with their Id and DisplayName.
2. SELECT Clause Subquery: For each of the 10 users from the derived table, the scalar subquery (select top 1 Name from dbo.Badges where UserId = Users.Id order by Date desc) runs. It looks up the Badges table and finds the single most recent badge name for that specific UserId.
3. Outer Query: The main query selects the Id and DisplayName from the derived table and the Latest_Badge calculated by the scalar subquery.

Practice Exercise

Time to put your knowledge to the test. Imagine you work for an e-commerce company and have access to three tables: customers, orders, and products.

Tables:

• customers (customer_id, name, city)
• orders (order_id, customer_id, product_id, quantity, order_date)
• products (product_id, product_name, price)

Your Task:
Write a single SQL query to find the names of all customers who live in 'São Paulo' and have placed an order for a product that costs more than the average price of all products.

Hints:

• You will need to filter customers by city.
• You will need to find which customers have placed an order for an expensive product. This involves a JOIN and another filter.
• The definition of "expensive" is dynamic (above average), so you'll need a subquery for that.

SELECT DISTINCT
    c.name
FROM
    customers AS c
JOIN
    orders AS o ON c.customer_id = o.customer_id
WHERE
    c.city = 'São Paulo'
AND
    o.product_id IN (
        SELECT
            product_id
        FROM
            products
        WHERE
            price > (SELECT AVG(price) FROM products)
    );

Conclusion

Great job! You've just unlocked a technique that dramatically increases the complexity of questions you can answer with SQL. By nesting queries within each other, you can create logical, sequential steps to solve analytical problems that would be impossible in a single-layer query.

Key Takeaways:

• Subqueries are queries within queries that help break down complex problems.
• FROM Clause (Derived Tables): Use these when you need to perform multi-stage aggregations. The inner query creates a temporary table for the outer query to use. Remember to give it an alias!
• WHERE Clause: Use these to create dynamic filters. The inner query can return a single value (for use with =, >, etc.) or a list of values (for use with IN).
• SELECT Clause (Scalar Subqueries): Use these to compute a new column for each row in your result set. The inner query must return a single value and is often correlated with the outer query.

In this lesson, you saw how subqueries can get nested and sometimes become difficult to read. In our next lesson, we will explore Common Table Expressions (CTEs). CTEs serve a similar purpose to derived tables but provide a way to name your subqueries and structure your complex queries in a much cleaner and more readable way.