• The way I always remember this is thus: If you refer to the right hand table anywhere is the where clause, you immediately turn that left join into a inner join. That's the effect you see anyway.

    Putting the predicate in the ON clause keeps it a left join.

    Unless of course you are doing an anti-join, where you want all records from Set A not in Set B, so you use

    select cols from Set_A a

    left join Set_B b

    On a.col1 = b.col1

    where b.col1 is NULL

    That is the only time I refer to a column from the right hand table involved in a left outer join in the where clause.

    HTH

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."