What is the logical Processing order of select statement

  • Comments posted to this topic are about the item What is the logical Processing order of select statement

  • Nice and easy question.. - thanks

    -----------------
    Gobikannan

  • Was pleased that I got it right whilst working it out in my head. That said I wonder if I would have got it right if there was no options to help my thought process.....:w00t:

  • There was a very good article from Celko over a while ago (yes he does write good articles, without abuse!), describing the sequence a little better than the article pointed to here.

    http://www.sqlservercentral.com/Forums/Topic1053018-392-1.aspx#bm1054071

    Steve Hall
    Linkedin
    Blog Site

  • My favourite kind of question, thanks!

    i.e. one I answer correctly quickly 😛

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • What bothers me (about the documentation referenced, not about this question) is the relative order of ON and JOIN - how can the ON be processed without first bringing in the joined table?

    * My guess as to the answer - I think that in the logical processing order as described on the MSDN page, the "FROM" step refers to bringing in ALL the tables (probably as a cartesian product), "ON" to filtering on matching rows, and "JOIN" to bringing back rows that were filtered out but should be brought back because of an outer join.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • This was removed by the editor as SPAM

  • Got a very similar question wrong over a year ago. Since then I have had a one page diagram of "Logical Query Processing" from Itzik Ben-Gan's book Inside Microsoft SQL Server 2008: T-SQL Querying posted on the wall of my cube.

    I Learned it and got it right this time. 🙂

    SSC QOD have been the root of much of my learning over the years.

  • Awesome question! Thanks.

    _________________________________
    seth delconte
    http://sqlkeys.com

  • Hugo Kornelis (5/3/2012)


    What bothers me (about the documentation referenced, not about this question) is the relative order of ON and JOIN - how can the ON be processed without first bringing in the joined table?

    * My guess as to the answer - I think that in the logical processing order as described on the MSDN page, the "FROM" step refers to bringing in ALL the tables (probably as a cartesian product), "ON" to filtering on matching rows, and "JOIN" to bringing back rows that were filtered out but should be brought back because of an outer join.

    I think you're correct. I remember when I was teaching Database Theory about a decade ago. And a theoretical Relational Algebraic Join was described as starting with a Cartesian Product of all the tables (FROM), then applying filters (ON) and returning the results (JOIN).

    Which are then mangled by WHERE, SELECT, etc.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • great question and dialogue - cheers

  • Great question about the basics.

  • Hugo Kornelis (5/3/2012)


    What bothers me (about the documentation referenced, not about this question) is the relative order of ON and JOIN - how can the ON be processed without first bringing in the joined table?

    * My guess as to the answer - I think that in the logical processing order as described on the MSDN page, the "FROM" step refers to bringing in ALL the tables (probably as a cartesian product), "ON" to filtering on matching rows, and "JOIN" to bringing back rows that were filtered out but should be brought back because of an outer join.

    I think you're right in that FROM refers to identifying all sources, not strictly parsing the initial FROM statement. Then JOIN refers to processing the (INNER|OUTER|CROSS) part, not strictly processing each JOIN clause.

    Remember, this is logical order, not actual order, so think in terms of designing/diagramming rather than actually querying. I'd turn your question around: Since each step only has the information from previous steps to work with, how can you JOIN without knowing what you're JOINing ON?

  • Daniel Bowlin (5/3/2012)


    Got a very similar question wrong over a year ago. Since then I have had a one page diagram of "Logical Query Processing" from Itzik Ben-Gan's book Inside Microsoft SQL Server 2008: T-SQL Querying posted on the wall of my cube.

    I Learned it and got it right this time. 🙂

    SSC QOD have been the root of much of my learning over the years.

    Hah! I have this same diagram hanging on the wall of my cube. I knew it would come in handy... 🙂

    I think I first heard of it on Jen McCown's blog here - http://www.midnightdba.com/Jen/2010/09/select-if-on-preceeds-join-then-how/

    -Dan B

  • Just read the first chapter of Itzik's book on TSQL, so very very easy question 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply