locked
Best use of Correlated subquery in sqlserver RRS feed

  • Question

  • Hi Everyone,

                      Can anyone tell me where and when correlated subquery suits really great with an example

    Thanks and Regards

    Chaithanya M


    Chaithu ---------------------------------------------------- Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Tuesday, December 20, 2011 6:11 AM

Answers

  • 1) Checking for existence

    SELECT * FROM Customers WHERE NOT EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID=Customers. CustomerID)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by skc_chat Tuesday, December 20, 2011 9:33 AM
    • Marked as answer by Kalman Toth Sunday, December 25, 2011 11:00 PM
    Tuesday, December 20, 2011 6:26 AM
    Answerer
  • Correlated subquery:    
    This is similar to a simple subquery except that it references at least one column in the outer query and so it cannot run separately by itself.
    The outer query runs first and the correlated subquery runs once for every row in the outer query.
    What makes correlated subqueries different from the nested subqueries we have been looking at is that the information travels in two directions
    rather than one. In a nested subquery, the inner query is only processed once, and that information is passed out for the outer query,
    which will also execute just once—essentially providing the same value or list that you would have provided if you had typed it in yourself.
    With correlated subqueries, however, the inner query runs on information provided by the outer query, and vice versa.
                    It works in a three-step process:
                    1.    The outer query obtains a record and passes it into the inner query.
                    2.    The inner query executes based on the passed in value(s).
                    3.    The inner query then passes the values from its results back out to the outer query, which uses them to finish its processing.

    -- nth highest salary   
        select  *
        from    dbo.employ E1
        where
        N = (select count(distinct(E2.sal)) from dbo.employ E2 where E2.sal >= E1.sal)

    • Proposed as answer by Naomi N Tuesday, December 20, 2011 2:58 PM
    • Marked as answer by Kalman Toth Sunday, December 25, 2011 11:00 PM
    Tuesday, December 20, 2011 6:57 AM
  • The following pages have examples for correlated subqueries:

    http://www.sqlusa.com/bestpractices/correlated/

    http://www.sqlusa.com/bestpractices/derivedcorrelated/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Proposed as answer by Naomi N Tuesday, December 20, 2011 2:58 PM
    • Marked as answer by Kalman Toth Sunday, December 25, 2011 11:00 PM
    Tuesday, December 20, 2011 8:06 AM

All replies

  • 1) Checking for existence

    SELECT * FROM Customers WHERE NOT EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID=Customers. CustomerID)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by skc_chat Tuesday, December 20, 2011 9:33 AM
    • Marked as answer by Kalman Toth Sunday, December 25, 2011 11:00 PM
    Tuesday, December 20, 2011 6:26 AM
    Answerer
  • Correlated subquery:    
    This is similar to a simple subquery except that it references at least one column in the outer query and so it cannot run separately by itself.
    The outer query runs first and the correlated subquery runs once for every row in the outer query.
    What makes correlated subqueries different from the nested subqueries we have been looking at is that the information travels in two directions
    rather than one. In a nested subquery, the inner query is only processed once, and that information is passed out for the outer query,
    which will also execute just once—essentially providing the same value or list that you would have provided if you had typed it in yourself.
    With correlated subqueries, however, the inner query runs on information provided by the outer query, and vice versa.
                    It works in a three-step process:
                    1.    The outer query obtains a record and passes it into the inner query.
                    2.    The inner query executes based on the passed in value(s).
                    3.    The inner query then passes the values from its results back out to the outer query, which uses them to finish its processing.

    -- nth highest salary   
        select  *
        from    dbo.employ E1
        where
        N = (select count(distinct(E2.sal)) from dbo.employ E2 where E2.sal >= E1.sal)

    • Proposed as answer by Naomi N Tuesday, December 20, 2011 2:58 PM
    • Marked as answer by Kalman Toth Sunday, December 25, 2011 11:00 PM
    Tuesday, December 20, 2011 6:57 AM
  • The following pages have examples for correlated subqueries:

    http://www.sqlusa.com/bestpractices/correlated/

    http://www.sqlusa.com/bestpractices/derivedcorrelated/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM
    • Proposed as answer by Naomi N Tuesday, December 20, 2011 2:58 PM
    • Marked as answer by Kalman Toth Sunday, December 25, 2011 11:00 PM
    Tuesday, December 20, 2011 8:06 AM
  • Correlated sub-queries are inner queries that use the context of the outer query. They are used when a sub-query is required, but a join just won't do. Examples include EXISTS(), IN(), and SELECT (SELECT ...) FROM ...

    When a correlated query is better than a join is based on the nature of the data and the INDEXing scheme.


    Tuesday, December 20, 2011 2:34 PM
  • CLEARLY EXPLAINED THANKS
    Saturday, March 4, 2017 5:16 PM