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
Now, take a look at this query:
Just by looking at the query, can we choose which of the three below is the correct statement?
- [All] will be left outer joined with [Odd] with predicates applied at query level
- [All] will be left outer joined with [Odd] with predicates applied at table level
- [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
Applying predicate at the query level has an effect of turning it into inner-join.
2. Table level
3. 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.