What is the logical Processing order of select statement

Question

Post reply

What is the logical Processing order of select statement

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Woot woot.

For any interested - Itzik has his poster posted here:

http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ken SSCoach Points: 17191 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

Nice,

Thanks Jason.

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

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: 150467 More actions · Answer 4

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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 6

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 7

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 8

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 9

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