• Yes, subtle and the reason that I (almost) never use a constant in the ON. By adding one more row to the original query it becomes clearer:

    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)

    insert b values (20, 1) -- The new row

    select *

    from a

    left join b

    on a.a1 = b.a1

    and a.a1 = 1

    drop table a

    drop table b

    a1 a2 b1 a1

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

    1 10 20 1

    2 20 NULL NULL

    This makes it a little easier to see that the ON is controlling which b rows join a, but not filtering the result set. (So, this is positive reinforcement to not do this.)