SELECT: If ON preceeds JOIN, then how….

I got some excellent questions from my 24HOP session yesterday, and when presenting the same session at NTSSUG last night.  There were a few questions pointing to the same area:

 * How is the ON clause evaluated before the JOIN clause?

 * If ON is evaluated before the JOIN, how does it resolve table aliases in the ON statement. They aren’t defined until the JOIN statement.

This poster, available on Itzik Ben-Gan’s website, clearly illustrates the answer: All JOINs (cross, inner and outer) first “multiply” the rows of the righthand table by the rows of the lefthand table…or, in short, the first phase of any join is to perform a cross join.   So, the logical processing order for SELECT should really look like this:

1. FROM
2. JOINs
   

   1. Perform cross join (cartesian product)
   2. ON filter (for INNER JOINs)
   3. Add outer rows (for OUTER JOINs)

3. WHERE
4. …

An OUTER join will walk through all three steps: cross join, ON filter, and add outer rows. An INNER join walks through the first two steps: cross join and ON filter.

That’s how the SQL engine resolves table aliases “before” the ON clause: because SQL first evaluates both tables, then filters that rowset by ON.  Sweet!

Hey, “Forgotten T-SQL” applies to me, too!

Happy days,

Jen

Related reading (on my blog, of course):

• N Things Worth Knowing About SELECT
• SELECT, Deconstructed
• 24HOP Session: Forgotten T-SQL (includes a download of the code I used)