Home » 24HOP »Beginner »Intermediate »NTSSUG »sql server »SQLPASS »sqlserverpedia-syndication »SSC »Tips »TSQL » Currently Reading:

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

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,


Related reading (on my blog, of course):

Currently there are "3 comments" on this Article:

  1. Matt Velic says:

    I have to say that I really enjoyed your presentation, not least of all because I’m reading through Itzik’s Inside Microsoft SQL Server 2008: T-SQL Querying book at the moment. I’m looking forward to seeing the full thing in November!

  2. [...] This post was mentioned on Twitter by Jen & Sean McCown, Jen & Sean McCown. Jen & Sean McCown said: Blog: SELECT: If ON preceeds JOIN, then how….: I got some excellent questions from my 24HOP session yesterday, and… http://bit.ly/dx4HHF [...]

Comment on this Article:

Release Date: Minion by MidnightDBA

Just 1 day 13 hours 36 minutes 20 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/