Friday 23 March 2012

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

 

6 comments:

  1. The correlated subquery may be replaced with a set comparison subquery or a join using an inline view for better efficiency. However even the correlated subquery's performance may be good in the case of use of "exists" clause with indexes created on the join columns in the correlated subquery. You may also find some excellent database related stuff here : crazy4db.blogspot.in

    ReplyDelete
  2. I have personally seen correlated sub queries provide tremendous improvements in speed. It all depends on whether the the join forces an index to be used. But, as you said, there are other ways to do this nowadays in SQL Server.

    ReplyDelete
    Replies
    1. Thanks "DavidX"
      I think "Anonymous" Already mentioned this in his comments. Please cross verifies it.

      Delete