LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

Subqueries and Correlated Subqueries in SQL Server

A subquery is a query inside another SQL query. A correlated subquery uses values from the outer query to check each row. Both help us solve complex database questions using smaller queries.

Subqueries and Correlated Subqueries in SQL Server

Quick Answer: A subquery is a query inside another SQL query. A correlated subquery uses values from the outer query to check each row. Both help us solve complex database questions using smaller queries.

1. Why Do We Need Subqueries in SQL Server?

Imagine Priya manages a grocery shop with 1,000 products. She wants to find products priced above the shop's average price.

She already stores every product and its price. However, she does not know the average price yet.

How can she compare every product without calculating the average manually?

The problem without subqueries

Priya can calculate the average first and store it separately. She can then use that value in another query.

Example 1: Find products above the average price without a subquery

This SQL Server example uses a variable to store the calculated average.

DECLARE @AveragePrice DECIMAL(10, 2);

SELECT @AveragePrice = AVG(Price)
FROM Products;

SELECT ProductName, Price
FROM Products
WHERE Price > @AveragePrice;

This approach works, but it needs two separate queries. It also needs a variable to store the average.

Now imagine Priya comparing products across many departments. Maintaining separate calculations becomes harder.

  1. We need extra variables for intermediate results.
  2. We may repeat similar calculations across different queries.
  3. We need to manage query execution in separate steps.
  4. Complex comparisons can become harder to maintain.

We need a way to place one query inside another. We also need a way to compare each row with related records.

The solution: Subqueries and correlated subqueries

Subquery = a query written inside another SQL statement.

Outer query = the main query that uses the subquery result.

Correlated subquery = an inner query that refers to the outer query's columns.

Priya can calculate the average inside the main query. She can also compare each product against its own category.

Animation 1: How a subquery works inside an outer query

Follow Priya's product comparison from the outer query to the final result.

Priya Grocery shop owner
checks product prices
Products Table Rice 1200 · Sugar 800 · Milk 600 · Curd 900
runs inner query
Inner Query Calculate average price = 875
passes result to outer query
Outer Query Compare each price with 875
returns matching products
Final Result Rice 1200 · Curd 900
Source data Outer query Current animation stage Query result

The inner query calculates the average. The outer query uses that value to find matching products.

2. Implement Subqueries in SQL Server

Subqueries = smaller queries placed inside another SQL statement.

SQL Server evaluates the inner query and uses its result in the outer query.

Use a subquery with the WHERE clause

Priya wants products priced above the overall average. We can calculate that average directly inside WHERE.

Example 2: Find products above the average and category average

Run this complete SQL Server script in one query window.

DECLARE @Products TABLE
(
    ProductName VARCHAR(30),
    Category VARCHAR(20),
    Price DECIMAL(10, 2)
);

INSERT INTO @Products VALUES
('Rice', 'Staples', 1200),
('Sugar', 'Staples', 800),
('Milk', 'Dairy', 600),
('Curd', 'Dairy', 900);

-- Non-correlated subquery
SELECT ProductName, Price
FROM @Products
WHERE Price > (SELECT AVG(Price) FROM @Products);

-- Correlated subquery
SELECT P.ProductName, P.Category, P.Price
FROM @Products P
WHERE P.Price > (
    SELECT AVG(I.Price)
    FROM @Products I
    WHERE I.Category = P.Category
);

The first query calculates the overall average price. It returns products priced above that average.

Expected output: Non-correlated subquery

ProductName  Price
-----------  ------
Rice         1200.00
Curd          900.00

The second query calculates the average for each product's category. It compares each product with its matching category.

Expected output: Correlated subquery

ProductName  Category  Price
-----------  --------  ------
Rice         Staples   1200.00
Curd         Dairy      900.00

Notice P.Category comes from the outer query. The inner query uses that value to calculate the matching category average.

Important: The overall average is 875. Staples average is 1000. Dairy average is 750. Both queries return Rice and Curd for this sample data, but their comparison rules are different.

3. Quick Summary

Subquery vs correlated subquery

Comparison of the two SQL Server query techniques
Subquery Correlated subquery
Works independently of the outer query. References columns from the outer query.
Does not depend on the current outer row. Its result depends on the current outer row.
Often calculates a shared value. Often calculates a row-specific value.
Example: Overall average price. Example: Average price within each category.

The important terms to remember

  • Subquery: A query inside another SQL statement.
  • Outer query: The main query using the inner result.
  • Correlated subquery: An inner query referencing the outer query.
  • Alias: A temporary name used for a table or column.
  • AVG: An aggregate function that calculates an average.

4. Common Mistakes and Best Practices

1. Returning multiple values with an equality operator

A scalar subquery used with = must return one value. Multiple returned rows can cause an error.

Better approach: Use IN when comparing against multiple returned values.

2. Forgetting the correlation condition

A correlated subquery needs the correct relationship with the outer query. Without it, comparisons may produce incorrect results.

Better approach: Check the inner query's reference to the outer table alias.

3. Assuming correlated subqueries are always slow

Performance depends on the query, indexes, data size and execution plan. SQL Server can optimize many correlated queries.

Better approach: Compare execution plans and test realistic data volumes.

Design tip: Use subqueries when they make the business rule easier to understand. Consider JOINs, CTEs and window functions when they offer a clearer solution.

5. How Subqueries Come Up in Interviews

Question 1: What is a subquery in SQL Server?

What the interviewer expects: Explain the inner and outer query relationship.

Strong answer: "A subquery is a query inside another SQL statement. For example, I can calculate the average grocery product price inside a WHERE condition and find products above that average."

Question 2: What is a correlated subquery?

What the interviewer expects: Explain its dependency on the outer query.

Strong answer: "A correlated subquery references columns from the outer query. In Priya's shop, I can compare each product's price with the average price of its own category."

Question 3: What is the difference between a subquery and a correlated subquery?

What the interviewer expects: Explain how their execution dependencies differ.

Strong answer: "A normal subquery does not depend on the current outer row. A correlated subquery uses values from the outer query, so its result depends on that row."

What interviewers expect at different levels

  • Junior developer: Write basic subqueries using WHERE and IN.
  • Senior developer: Solve correlated queries and compare alternative approaches.
  • Technical architect: Evaluate query plans, scalability and database design trade-offs.

6. Hands-On Practice

Beginner: School student marks

Find students whose marks are greater than the average marks. Use a subquery with AVG.

Expected result: Return students scoring above the class average.

Intermediate: Hospital departments

Find hospital staff whose salary exceeds their department's average salary. Use a correlated subquery.

Hint: Compare each staff member's DepartmentId with the inner query.

Advanced: Bank transactions

Find customers whose transaction amount exceeds their own average transaction amount.

  • Create Customers and Transactions tables.
  • Insert multiple transactions for each customer.
  • Use a correlated subquery to calculate each customer's average.

Challenge: Write an alternative solution using a CTE and compare both execution plans.

7. Next Steps and Community

Previous article: Aggregate Functions, GROUP BY and HAVING in SQL Server

Next article: Common Table Expressions (CTE) and Recursive CTE

For more details, read the official Microsoft documentation:

Try Priya's grocery example with more product categories. Share your queries and compare subquery solutions with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.