Understanding NON-ANSI join

If SQL Server 2005 is what we are talking about, then the stars denoting join operators are not stars at all, because compatibility level 90 does not accept non-ansi join, and it does so with a reason: if we look at it, non-ansi join is certainly ambiguous.

Let’s say we have 2 tables, I’ll call it [All] and [Odd], where [All] contains rows with ids 1 to 10, and [Odd] contains rows with ids with odd numbers from 1 up to 10, as presented below

[All] table
[All] table
[Odd] table
[Odd] table

Now, take a look at this query:

Original query text

Just by looking at the query, can we choose which of the three below is the correct statement?

  1. [All] will be left outer joined with [Odd] with predicates applied at query level
  2. [All] will be left outer joined with [Odd] with predicates applied at table level
  3. [All] will be left outer joined with [Odd] with predicates applied at join level

Without taking a guess, I cannot tell, and that is the reason why I associated this type of join with the word ‘ambiguous’, and to make this query compatible with SQL 2005 (90), we have no choice but to convert this join to ANSI join. The problem is, different processes give different results. Let’s walk through the query plan and results of each statement above.

1. Query level

Join then filter

Applying predicate at the query level has an effect of turning it into inner-join.

2. Table level

table level

3. Join level

Join level

Now let’s look at the query plan if we executed the non-ansi join with SQL compatibility level 80:

The condition that resembles this original query plan is applying the predicates at the table level.


Posted on April 10, 2008, in Database. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: