Suggested Videos
Part 57 - Transactions
Part 58 - Transaction Acid Tests
Part 59 - Subqueries
In this video we will discuss about Corelated Subqueries
In Part 59, we discussed about 2 examples that uses subqueries. Please watch Part 59, before proceeding with this video. We will be using the same tables and queries from Part 59.
In the example below, sub query is executed first and only once. The sub query results are then used by the outer query. A non-corelated subquery can be executed independently of the outer query.
Select [Id], [Name], [Description]
from tblProducts
where Id not in (Select Distinct ProductId from tblProductSales)
If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery. In the where clause of the subquery below, "ProductId" column get it's value from tblProducts table that is present in the outer query. So, here the subquery is dependent on the outer query for it's value, hence this subquery is a correlated subquery. Correlated subqueries get executed, once for every row that is selected by the outer query. Corelated subquery, cannot be executed independently of the outer query.
Select [Name],
(Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity
from tblProducts
order by Name
Part 57 - Transactions
Part 58 - Transaction Acid Tests
Part 59 - Subqueries
In this video we will discuss about Corelated Subqueries
In Part 59, we discussed about 2 examples that uses subqueries. Please watch Part 59, before proceeding with this video. We will be using the same tables and queries from Part 59.
In the example below, sub query is executed first and only once. The sub query results are then used by the outer query. A non-corelated subquery can be executed independently of the outer query.
Select [Id], [Name], [Description]
from tblProducts
where Id not in (Select Distinct ProductId from tblProductSales)
If the subquery depends on the outer query for its values, then that sub query is called as a correlated subquery. In the where clause of the subquery below, "ProductId" column get it's value from tblProducts table that is present in the outer query. So, here the subquery is dependent on the outer query for it's value, hence this subquery is a correlated subquery. Correlated subqueries get executed, once for every row that is selected by the outer query. Corelated subquery, cannot be executed independently of the outer query.
Select [Name],
(Select SUM(QuantitySold) from tblProductSales where ProductId = tblProducts.Id) as TotalQuantity
from tblProducts
order by Name
i think the examples have been interchanged here
ReplyDeleteYour tutorials are really good for understanding the concepts.
ReplyDeleteThank you so much for all your efforts.