Re: using NOLOCK cause Cartesian joins

On Wed, 12 Nov 2003, Roger Gordon wrote:

> When we use WITH (NOLOCK) at the end of table list in a SELECT query, a
> Cartesian join is returned (i.e. approximately  6,500 records instead of
> 57, which is distressing.

I'm guessing you mean something like:
select *
from table1, table2 WITH (NOLOCK)
where table1.foo=table2.bar;
?

If so, it should be giving you a notice like:
adding missing FROM-clause entry for table "table2"
because you've aliased table2 as the name WITH and the first column
of said table as NOLOCK (if I'm remembering the details correctly)
so table2 isn't in scope in the where clause.

As far as we can tell a query like the above is technically illegal in
SQL but 7.3 and earlier will always add an extra entry as if you had
done from table1, table2 WITH (NOLOCK), table2.  7.4 has an option to
instead error.