Subqueries in SELECT

The Core Principle: The Scalar Subquery

Before we write any code, let's establish the single most important rule for this technique. A subquery used in a SELECT clause must return exactly one column and one row. This is known as a scalar subquery.

From a logical, low-level perspective, this makes perfect sense. The SELECT statement defines the columns of your final result set. Each "cell" in that result set (the intersection of a row and a column) can only hold a single value. Therefore, any subquery that generates a value for a column must resolve to a single, scalar value.

Let's begin by reading a quick definition of a scalar subquery.

Use Case 1: The Uncorrelated Subquery

The simplest way to use a scalar subquery in SELECT is to calculate an overall aggregate value and display it next to each row of your main result. For example, you might want to show each employee's salary and, in the next column, the average salary for the entire company.

This type of subquery is uncorrelated—it doesn't depend on the outer query and is executed only once.

Let's watch a video that demonstrates this concept visually.

As the video explained, the database engine follows a logical process:

1. It first executes the independent subquery, e.g., (SELECT AVG(Salary) FROM Employees).
2. The single result (e.g., 47909) is stored.
3. Then, it executes the outer query, e.g., SELECT EmployeeID, Salary FROM Employees.
4. For every row returned by the outer query, it appends the stored result from step 2 as a new column.

This is efficient because the subquery calculation happens only once.

SQL's Order of Execution

You might wonder how the database can execute the SELECT subquery first. It helps to understand the logical order of operations in a SQL query, which often differs from the order you write it in.

SELECT
    product_name,
    list_price,
    (SELECT AVG(list_price) FROM products) AS avg_company_price
FROM
    products;

Use Case 2: The Correlated Subquery

Now, let's connect back to our previous lesson. Just like in the WHERE clause, a subquery in the SELECT clause can also be correlated. This is far more powerful because it allows you to calculate a unique value for each row of the outer query.

For example, instead of the company-wide average salary, you could display the average salary for each employee's specific department right next to their own salary.

Let's look at an excellent example of this.

The logic here mirrors what you learned in the last lesson, but the result is displayed instead of used for filtering:

1. The outer query gets a row (e.g., employee 'Alice').
2. The correlated subquery runs, but with Alice's ID: SELECT MAX(order_date) FROM orders WHERE employee_id = 'Alice.id'.
3. The subquery returns Alice's latest order date, which is placed in the last_order_date column for her row.
4. The process repeats for the next employee, 'Bob', triggering the subquery again with his ID.

This row-by-row execution is powerful but can be slow on large tables, a trade-off you must always consider.

Here's another example using the AdventureWorks sample database to calculate the total sales for each customer.

When to Use and When to Be Cautious

Subqueries in the SELECT clause are a fantastic tool, but they are not always the best solution. Knowing the trade-offs is a key job-ready skill.

In many cases, especially with correlated subqueries on large datasets, a JOIN or a Window Function (which we'll cover in a future module) can be significantly more performant. For a quick preview of how this technique compares to a Window Function, you can watch the following short clip.

Conclusion

You have now added another powerful subquery technique to your SQL toolkit. By placing a scalar subquery in the SELECT clause, you can create new columns on the fly, enriching your query results with valuable context.

Key Takeaways:

• A subquery in a SELECT clause is used to create a new calculated column.
• It must be a scalar subquery, meaning it returns only a single value (one row, one column).
• Uncorrelated subqueries are executed once, and their single result is appended to every row. They are useful for showing global aggregates.
• Correlated subqueries are executed for each row of the outer query. They are powerful for fetching row-specific data but can be slow on large tables.
• Always give your subquery column a clear alias using AS for readability.

Preview of the Next Lesson:
We've now used subqueries in the WHERE and SELECT clauses. What about the FROM clause? In the next lesson, we will learn how to place a subquery in the FROM clause, treating the result of one query as a temporary table (a "derived table") that you can then query from. This is a fundamental technique for breaking down complex analytical problems into logical steps.