Itzik Ben-Gan wrote an article that explains this very clearly in SQL Magazine in the October 2004 issue. The InstantDoc ID is #43681.

The problem is that in Old Style Joins, you can't separate the join clause and the Where Clause so the result may include extraneous NULL values in your result set.

Here are two simple queries you can run in PUBS to see this:

-- Old Style Join

-- 13 Valid Entries, 6 Invalid Entries

select o.name, i.name,i.indid

from sysobjects o, sysindexes i

where o.id *= i.id and

o.type = 'S' and

i.indid = 1

-- New Style Join

-- 13 Valid Entries, 0 Invalid Entries

select o.name, i.name,i.indid

from sysobjects o

left outer join sysindexes i

on o.id = i.id

where o.type = 'S' and

i.indid = 1