"Andrew Hargreaves" <andrew.hargreaves@stripped> wrote on 08/16/2005
> Can anyone explain why this SQL statement would fail? A friend was asked
> in a pre-interview assessment and it threw him a bit.
> SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr
> 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.