You know that I have high regard for your T-SQL understanding.

But, sorry, but I don't think this needs a warning any more than many other sneaky behaviors of SQL Server. At least this one is syntactically defined and is not an optimizer decision.

The two ways of expressing this query give two differently purposed result sets. Although I agree that it can be surprising, I do not agree that the constant in the ON clause is meaningless.

-- Give me all 'a' rows.

-- If a.a1 = 1 = b.a1

-- also join a 'b' row to my 'a' row

select *

from a

left join b

on a.a1 = b.a1

and a.a1 = 1

a1 a2 b1 a1

--- --- --- ---

1 10 20 1

2 20 NULL NULL

-- Give me all 'a' rows where 'a.a1' = 1.

-- If a.a1 = b.a1 (and thus = 1)

-- also join a 'b' row to my 'a' row

select *

from a

left join b

on a.a1 = b.a1

WHERE b.a1 = 1

a1 a2 b1 a1

--- --- --- ---

1 10 20 1

Then, just for fun use a FULL OUTER JOIN on the first query.

-- Give me all 'a' rows and all 'b' rows

-- If a.a1 = 1 = b.a1 join a 'b' row to my 'a' row

-- Else show the 'a' and 'b' rows independently.

select *

from a

left join b

on a.a1 = b.a1

and a.a1 = 1

a1 a2 b1 a1

--- --- --- ---

1 10 20 1

2 20 NULL NULL

NULL NULL 2 2

But it has been a long time since I thought about this, so thanks for bringing up the problem. It definitely should be in the basic course materials for any T-SQL querying classes and emphasized with a few good examples.