Subqueries

A Subquery is a query nested inside another query. They are powerful tools for performing multi-step operations in a single statement.

Definition: Types of Subqueries

Subqueries are categorized by where they are used and what they return.

1. Scalar Subqueries

Returns exactly one value (one column, one row). It can be used anywhere a literal value is used (like in the SELECT list or a WHERE clause).

2. Table Subqueries (Derived Tables)

Returns an entire table (multiple columns and/or rows). These are used in the FROM clause and must be given an alias.

3. Correlated Subqueries

A subquery that references a column from the outer query. Unlike standard subqueries, it cannot be run independently.

Context: EXISTS vs. IN

This is a classic performance debate.

• IN: The subquery is run once, and the results are stored in a list. The outer query then checks its rows against this list.
• EXISTS: The subquery is run for every row in the outer query. It stops as soon as it finds a single match (Short-circuiting).

Tip

For large datasets, EXISTS is generally faster than IN because it doesn’t need to build and store the entire list of values in memory.

Detailed Explanation: Syntax and Examples

Scalar Subquery Example

Find employees who earn more than the company average.

Scalar Nesting

SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Table Subquery Example

Find the maximum of a set of averages.

Derived Table

SELECT MAX(AvgSalary)
FROM (
  SELECT Dept, AVG(Salary) AS AvgSalary
  FROM Employees
  GROUP BY Dept
) AS DepartmentStats;

Correlated Subquery Example

Find products that are priced higher than the average for their specific category.

Correlated Logic

SELECT p1.Name, p1.Price
FROM Products p1
WHERE p1.Price > (
  SELECT AVG(p2.Price)
  FROM Products p2
  WHERE p2.Category = p1.Category
);

Example: The “Existence” Pattern

We want to find Customers who have placed at least one order.

EXISTS Pattern

SELECT Name
FROM Customers c
WHERE EXISTS (
  SELECT 1
  FROM Orders o
  WHERE o.CustomerID = c.ID
);

Under the Hood: The Optimizer’s View

How does the database execute a subquery?

1. Flattening

Modern query optimizers are very smart. Often, they will “flatten” a subquery into a standard JOIN if it’s more efficient.

2. The Loop Trap

Correlated subqueries are often slow because, in the worst-case scenario, the database performs a Nested Loop. If the outer table has 100,000 rows, the subquery might be executed 100,000 times.

3. Materialization

For non-correlated subqueries in the FROM clause, the database might “materialize” the subquery—meaning it runs the subquery, saves the result to a temporary table in memory, and then joins that temp table to the rest of your query.

Danger

Avoid deep nesting. While SQL allows many levels of subqueries, it makes code impossible to debug and often leads to catastrophic performance. If you find yourself nesting more than two levels deep, consider using a CTE (see next chapter).