In SQL Server, a subquery is a query that is nested inside another query and is executed first to produce a result set that is used by the outer query. On the other hand, a correlated subquery is a subquery that is related to the outer query such that it must be executed for each row processed by the outer query.
Let’s look at an example to understand this difference.
Consider two tables: ‘Sales‘ and ‘Products‘. ‘Sales‘ table has the following columns: ‘SaleID‘, ‘ProductID‘, ‘SaleDate‘, and ‘SalePrice‘. The ‘Products‘ table has the following columns: ‘ProductID‘, ‘ProductName‘, ‘ProductCost‘.
To fetch all the sales records in which the sale price is greater than the average product cost, we can use a subquery as follows:
SELECT SaleID, ProductID, SaleDate, SalePrice
FROM Sales
WHERE SalePrice > (SELECT AVG(ProductCost) FROM Products)In this query, the inner subquery ‘(SELECT AVG(ProductCost) FROM Products)‘ is executed first to calculate the average product cost. The result of this subquery is used to filter the records in the outer query.
Now, let’s consider another example. Suppose we want to fetch all products whose product cost is less than the average cost of all products in the same product category. To achieve this, we need to use a correlated subquery. The query would look like this:
SELECT ProductID, ProductName, ProductCost
FROM Products p1
WHERE ProductCost < (SELECT AVG(ProductCost)
FROM Products p2
WHERE p1.ProductID = p2.ProductID)In this query, the inner subquery ‘(SELECT AVG(ProductCost) FROM Products p2 WHERE p1.ProductID = p2.ProductID)‘ is correlated with the outer query by the condition ‘p1.ProductID = p2.ProductID‘. This means that the subquery needs to be executed for each row in the ‘Products‘ table.
In summary, the main difference between a subquery and a correlated subquery is that a subquery is executed only once, whereas a correlated subquery is executed for each row processed by the outer query.