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

 

Posted by: MR. JOYDEEP DAS