>>Kevin,
>>
>>Of course you would not have posted it if not everyone expects a result of 1 record, so I ruled that one out inmediately :)
>>
>>After 2 minutes, I saw there is an unfortunate typo: VendorPk ISO VendorFk in the subquery.
>>Without trying I guessed this would either return all rows or an error message as the field is not part of the table in the subquery and I was not sure where this would raise a correlating field error message or not.
>>
>>After trying I saw it did return all rows. I guess this behavior is because of Microsoft implementation. It would not surprise me if it would fail in another SQL dialect.
>>This can be the source of bugs that are very hard to track down.
>>
>
>Thanks to everyone for responding.
>
>Yes, it was a typo, but an intentional one. Correct, it returns all rows. The other intentional problem is not using alias references all the time - if you have a column in a derived table subquery that doesn't exist in the subquery, but where the column name DOES exist with respect to the table alias used on the outside, SQL Server basically permits it when the entire thing runs, and essentially uses the value from the "outside". Somewhere internally they relax a validation on resolving names
>
>Had I used a reference in the subquery....for instance ( select OrderTable.VendorPK from dbo.VendorTable), I would have gotten an error when I ran the entire thing.
>
>It's part of a lesson (which I know many realize) about ALWAYS using table alias references for every column. Aside from readability and maintenance, it also helps to avoid this issue in the MS implementation. I know a few developers who have been bit by this because they didn't use alias references all the time, and also because they didn't test the execution of the derived table subquery independently of the full outer query.

There is an alternative. We do not use table references in fields. In our database the first three characters refer to the table it belongs to. For example:

Ord_pk
Ord_VenFk
Ord_Qty

For the orders table and

Ven_pk
Ven_Name

For the vendors table.

Each and every fieldname has a unique name in the database and because of the three character prefix you always know where it comes from. It works very good for me, because it help to read, write, debug and analyze SQL statements.

Walter,