A follow-up observation. Based on the explanation in BOL for outer joins, any criteria of inner join table column to a constant in an ON clause of an outer join is meaningless.

That being the case, why is there no warning to that effect generated by SQL, similar to the warning generated when an outer-joined table is later inner-joined in the query?

I would go a step further and state that such syntax (in an outer join ON clause, inner-join table column to constant) should generate a compile-time error. If the purpose of the JOIN clause is simply to put two tables together, yet not limit the number of rows put together in the process, then specifying such a statement intended to limit rather than join should be illegal.

But here's where I really get on my soapbox. Let's slightly alter the syntax of the join, replacing the LEFT JOIN with a JOIN (and using 2 as the constant so we get something back):

[font="Courier New"]

create table a (a1 int primary key, a2 int)

create table b (b1 int primary key, a1 int)

insert a values (1, 10)

insert a values (2, 20)

insert b values (2, 2)

select *

from a

join b

on a.a1 = b.a1

and a.a1 = 2

[/font]

Results:

a1a2b1a1

22022

In this case, the ON criteria a.a1 = 2 limited results rather than linked rows from two tables, and it works as expected.

Consider the ramifications. A statement exists in a query where, in an INNER JOIN, one of the first table's columns is joined to a constant (or T-SQL @variable) as limiting criteria in the ON clause. Testing reveals situations where rows do not exist in the second table, failing the join, so "INNER JOIN" is replaced by "LEFT JOIN" . Poof! The limiting criteria in the ON clause just became meaningless!

My colleague and I are in shock, wondering how many of our procedures coded over the years have this subtle issue in outer joins.

Are we just dense?

Vince