Hello, welcome to Apploveworld Technologies, In this post, we’ll discuss Correlated subquery in SQL server.
In the previous post subquery series, we have discussed the basics of the subquery. We also discussed how subqueries can be replaced using join. If you haven’t read that, I would strongly encourage you to read that post so before continuing with this post.
Correlated subqueries are very simple and easy to understand. if the subqueries depend on the Outer Query for its values, then that subquery is called Correlated subquery.
First Let’s look at Non-Correlated subquery
We will be using the same tables that we have used in the previous post of this series, TblProducts and TblProductSale.
TblProducts Table contains the product data and TblProductSale table content sales-related data.So every time we sell a product, an entry will be made into the TblProductSale table.
Now let’s say I want to retrieve all the products that have not been sold at least once. How do I write that query? Obviously, whenever we make a sale and entries made into the TblProductSale sales table, and if you look at the sample data that we have, we haven’t sold a Cameras at least once.
Now, look at this. how does this query gets executed?
The subquery gets executed first. So if you look at this query, the subquery can be executed independently.
This is an example of a non-correlated subquery. In a non-correlated subquery the subquery is not dependent on the outer query for its values. The subquery can be executed independently on its own. once query is executed, the results of the subquery are then substituted within those parentheses for use by the Outer query.
Now let’s look at an example of a non-correlated subquery again will be using the same examples that we have discussed in the previous session
Let’s say, for example, I want to find out, all the products that we have sold and the total quantity.
I want to sum up the quantity sold for each product. So the productID is going to come from the TblProducts table.
So every time we select a row from the outer query, now the subquery gets executed.
if you look at this now, can I execute subquery independently outer query?
No, I can’t look at this. When I try to execute that, I get an error
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier “tblProducts.Id” could not be bound.
So that’s a correlated subquery because the subquery is dependent on the outer query for its value, for the value, for column productId.
this subquery dependent on the outer query for its value, in this case for productId. So here the subquery is dependent on the outer query for its value hence subquery correlated subquery.
Correlated subqueries get executed for every row that is selected by the outer query in our select statement. A correlated sub query cannot be executed independently of the outer query, but whereas a non-correlated subquery can be executed independently of the outer query.