locked
correlated subquery RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    Who can tell me why below T-SQL can return the maximum order ID for each customer, what's its execution process? such as: what's the first step, the second step, .....

    SELECT orderid, custid
FROM Sales.Orders AS O1
WHERE orderid = (SELECT MAX(O2.orderid)
                 FROM Sales.Orders AS O2
                 WHERE O2.custid = O1.custid);

    Thanks very much

    Jed

    Tuesday, June 5, 2012 10:13 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    Logically, in a correlated subquery, the inner query first evaluate for each row in the outer query. In your case for each CustID from outer table O1, the inner query finds the MAX(OrderID) for that CustID and filter the outer query based on the maximum of OrderID.

    Physically, this can be processed in a wide variety of ways based on the indexes present and other factors. Physical execution can be obtained from reviewing the execution plan of the query

    Krishnakumar S

    • Proposed as answer by Hasham NiazEditor Tuesday, June 5, 2012 12:45 PM
    • Marked as answer by Jeddd Tuesday, June 5, 2012 1:41 PM
    Tuesday, June 5, 2012 10:54 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    You can see the execution plan in SSMS to see how the queries are executed.

    In correlated sub queries, sub-queries will be evaluated for each outer rows. so if you have 100 rows in the Orders tables, The sub-query will be evaluated 100 times. I hope this is the clarification you wanted

    Regards
    Satheesh

    • Marked as answer by Jeddd Tuesday, June 5, 2012 2:24 PM
    Tuesday, June 5, 2012 1:32 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    You can see the execution plan to understand the way the query is getting executed.

    To get the execution plan

    Go to Query menu in the SSMS and choose Display estimated execution plan

    Tuesday, June 5, 2012 10:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Whenever we execute any query, it's logical order is:

    FROM-> WHERE -> GROUP BY -> Having -> Select

    In your case, when where is avalauated, it is taking maximum order id for a customer as per your query.

    For more information related to query execution, you can enable "Actual Execution plan" in SSMS while executing the query. It will display how database engine is executing the query - which index is used, which operator is used etc.. 

    Tuesday, June 5, 2012 10:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Run the query in SSMS. Activate the option Include Actual Execution Plan in Query menu before executing it.
    Tuesday, June 5, 2012 10:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    you can do 2 things:

    1. add set statistics profile on; at the beginning of your query. you can get a tabular view of what the execution order is from bottom up in the returned table.

    2. use your SSMS press CTRL + L and you can see a execution plan, you can read from right to left.

    Tuesday, June 5, 2012 10:48 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Logically, in a correlated subquery, the inner query first evaluate for each row in the outer query. In your case for each CustID from outer table O1, the inner query finds the MAX(OrderID) for that CustID and filter the outer query based on the maximum of OrderID.

    Physically, this can be processed in a wide variety of ways based on the indexes present and other factors. Physical execution can be obtained from reviewing the execution plan of the query

    Krishnakumar S

    • Proposed as answer by Hasham NiazEditor Tuesday, June 5, 2012 12:45 PM
    • Marked as answer by Jeddd Tuesday, June 5, 2012 1:41 PM
    Tuesday, June 5, 2012 10:54 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi,

    You can see the execution plan in SSMS to see how the queries are executed.

    In correlated sub queries, sub-queries will be evaluated for each outer rows. so if you have 100 rows in the Orders tables, The sub-query will be evaluated 100 times. I hope this is the clarification you wanted

    Regards
    Satheesh

    • Marked as answer by Jeddd Tuesday, June 5, 2012 2:24 PM
    Tuesday, June 5, 2012 1:32 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Logically, in a correlated subquery, the inner query first evaluate for each row in the outer query. In your case for each CustID from outer table O1, the inner query finds the MAX(OrderID) for that CustID and filter the outer query based on the maximum of OrderID.

    Krishnakumar S, thanks soooooo much for your detail exlaination, now I know the logic of this query.


    Tuesday, June 5, 2012 1:41 PM