SELECT, Deconstructed

Today let’s expand on the logical processing order of SELECT that I mentioned in last week’s N Things Worth Knowing About SELECT blog.

We’re looking at the SELECT statement clauses in the order that the SQL engine logically processes them…we’ll even write it that way – it’ll look weird, but we’ll be reading it like the SQL engine does.

You can find more on SELECT’s logical processing order in BOL, and in Itzik Ben-Gan’s T-SQL Fundamentals and T-SQL Programming books.

Here’s our basic T-SQL query (using AdventureWorks):

SELECT P.Name ,
 P.ProductNumber ,
 P.Color ,
 P.StandardCost ,
 SC.Name [Category]
FROM Production.Product P
LEFT OUTER JOIN Production.ProductSubCategory SC ON P.ProductSubCategoryID = SC.ProductSubCategoryID
WHERE P.ProductSubCategoryID IS NOT NULL

If we get all pseudo-mathematical on this, we can say that there’s some theoretical set of rows that we’ll get back out of this query; each logical step – in order – further refines that set of rows until we get back the actual rowset we want.

FROM

The SQL engine starts with the FROM clause first, to see where the data’s coming from. In this case we’re pulling from the Product table…easy. So, the theoretical rowset right now is everything in the Product table. (You can think about each step passing on that theoretical rowset to the next step for further refinement.)

-- Logical order:

FROM Production.Product P

ON/JOIN
(more..)

Read more: SQL Server Pedia

Posted via email from .NET Info