Subqueries are a great way to easily increase the scope of your result set, and in this post I’m going to explain the difference between Correlated and Noncorrelated Subqueries, as well as provide examples of them both.

Correlated Subqueries

Correlated Subqueries create a link between the outer query and the subquery. This means that the subquery is dependent on the outer query, and won’t execute as a stand alone query.

SELECT *
 
FROM Products AS P1
 
WHERE P1.Price >
              (
               SELECT AVG(P2.Price)
               FROM Products AS P2
               WHERE P2.ProductCategoryId = P1.ProductCategoryId
              )

The above query will return all Products that have a price higher than the average price for each product category. The WHERE clause of the subquery almost acts as if it were a GROUP BY for the outer query in this instance. This is because the subquery and outer query join together on the ProductCategoryId – meaning that the average price is found for each Product Category. Then products with a price greater than the average, for that product category, will be returned.

Noncorrelated Subqueries

Noncorrelated Subqueries are completely independent of the outer query, and will execute as a stand alone query without any issues. These are commonly used within a WHERE clause – much like the example below:

SELECT *
 
FROM Products AS P
 
WHERE P.ProductId IN (SELECT DISTINCT O.ProductId FROM Orders AS O)

The above code will return all rows from the Products table that have atleast one reference in the Orders tables. It does this by returning all of the ProductId’s in the Orders table, to be used in the WHERE clause of the outer query.