Normal Vs Correlated Sub Query
In this article I am trying to explain related to normal and correlated subquery. It is a short article but gives you a solid concept related to it.
Normal or no correlated subquery
In normal or no correlated subquery, the inner subquery portion is independent; it not depends on outer query. The result of inner query helps to execute the outer query. The outer query is depends on the result or output of the inner query. The inner query is completely independent and can execute independently.
USE Products
SELECT *
FROM Product AS P1
WHERE P1.Unit = (SELECT MAX(P2.Unit)
FROM Product AS P2
WHERE P2.ProductName = 'Tooth Brush');
Correlated or repeatingsub
But in the correlated or repeating subquery, the subquery is depends on outer query for it's values. This means the subquery is executed repeatedly once for each row that might be selected by the outer query.
USE Products
SELECT *
FROM Product AS P1
WHERE P1.Unit = (SELECT MAX(P2.Unit)
FROM Product AS P2
WHERE P2.ProductName = P1.ProductName);
Summary related to Performance
Subqueries are a powerful feature of SQL. However, subqueries that contain an outer reference can be very inefficient. In many instances these queries can be rewritten to remove the outer reference which can improve performance. It is worthwhile to review the SQL Execution plan to help identify potential inefficiencies.
Hope you like that
