May 2, 2012 at 10:59 pm
Comments posted to this topic are about the item What is the logical Processing order of select statement
May 2, 2012 at 11:52 pm
Nice and easy question.. - thanks
-----------------
Gobikannan
May 3, 2012 at 1:31 am
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:
May 3, 2012 at 1:39 am
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
May 3, 2012 at 3:10 am
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]
May 3, 2012 at 3:55 am
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.
May 3, 2012 at 5:24 am
This was removed by the editor as SPAM
May 3, 2012 at 7:05 am
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.
May 3, 2012 at 7:12 am
Awesome question! Thanks.
_________________________________
seth delconte
http://sqlkeys.com
May 3, 2012 at 7:16 am
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.
May 3, 2012 at 9:12 am
great question and dialogue - cheers
May 3, 2012 at 9:55 am
Great question about the basics.
May 3, 2012 at 10:18 am
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?
May 3, 2012 at 11:37 am
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
May 3, 2012 at 11:55 am
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 27 total)
You must be logged in to reply to this topic. Login to reply