"Andrew Hargreaves" <[EMAIL PROTECTED]> wrote on 08/16/2005 09:13:56 AM:
> Can anyone explain why this SQL statement would fail? A friend was asked it > in a pre-interview assessment and it threw him a bit. > > SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 > Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from > PrsnAddress T2) > > Several people have already responded with the comma vs. alias (T1.BldgName...). Moving on... I believe that if there is a single record in PrsnAddress, the whole query will return nothing. That is because the inner query of the EXISTS clause is not linked to any term from the outer query (as others have mentioned). However, the converse should be true that if PrsnAddress is empty, then you will get the results you expect (WHERE T1.City='London'...) because the NOT EXISTS would evaluate as TRUE. This is a good example why all developers should test for both positive and negative results from their queries. If someone had only tested with an empty PrsnAddress table, they could have been tricked into thinking the query operated correctly.
