What is the logical Processing order of select statement

Question

What is the logical Processing order of select statement

Viewing 12 posts - 16 through 27 (of 27 total)

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

Ken SSCoach Points: 17191 More actions · Answer 1

SQLRNNR (5/3/2012)
Woot woot.
For any interested - Itzik has his poster posted here:
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

Nice,

Thanks Jason.

seth delconte SSCertifiable Points: 6388 More actions · Answer 2

SQLRNNR (5/3/2012)
Woot woot.
For any interested - Itzik has his poster posted here:
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

Wonderful. Thanks!

_________________________________
seth delconte
http://sqlkeys.com

Paul White SSC Guru Points: 150468 More actions · Answer 3

mtassin (5/3/2012)
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.

I agree, and recently posted detailed examples of the logical trees showing exactly this arrangement:

http://sqlblog.com/blogs/paul_white/archive/2012/04/28/query-optimizer-deep-dive-part-1.aspx

There again, in SQL Server at least, using SQL-92 syntax does attach the ON clause more closely than SQL-89 syntax (also in the link above).

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Revenant SSC-Forever Points: 42467 More actions · Answer 4

Revenant

SSC-Forever

Points: 42467

May 7, 2012 at 8:30 am

#1484078

Nice and straightforward - thanks!

Olga B SSCommitted Points: 1529 More actions · Answer 5

The question was quick and easy. It's the last line in the explanation, about alias column names in WHERE that made me go, "Oh!" One of those things I knew, but never thought about.

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 6

Hmmm knew the order but didn't know that we can't use alias in Where clause.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

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 initially thought the order of ON and JOIN was screwy, but I can think of one supporting argument.

SELECT *

FROM tbl1 a

LEFT JOIN tbl2 b ON a.ID = b.ID AND b.column = 'value'

WHERE a.column > 0

The non-join conditions in the ON clause must be applied when the rows are first read, before doing the JOIN and applying the join conditions.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 8

SQLRNNR (5/3/2012)
Woot woot.
For any interested - Itzik has his poster posted here:
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

I also have this poster on my desk since long so got it right...

Thanks

srikant maurya Hall of Fame Points: 3293 More actions · Answer 9

Logical Processing Order of the SELECT statement

http://msdn.microsoft.com/en-us/library/ms189499.aspx

zymos SSCommitted Points: 1960 More actions · Answer 10

Thanks for the question.

More on the topic also at url: http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 11

Good question. Thanks for submitting.

http://brittcluff.blogspot.com/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 12

Dineshbabu

Hall of Fame

Points: 3220

January 9, 2013 at 5:36 am

#1575192

Easy One..

--
Dineshbabu
Desire to learn new things..

What is the logical Processing order of select statement

Cookies on SQLServerCentral