List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 16 2005 2:04pm
Subject:Re: Why does this fail
View as plain text  
"Andrew Hargreaves" <andrew.hargreaves@stripped> wrote on 08/16/2005 
09:13:56 AM:

> 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.

Why does this failAndrew Hargreaves16 Aug
  • Re: Why does this failRaz16 Aug
    • RE: Why does this failAndrew Hargreaves16 Aug
  • RE: Why does this failJ.R. Bullington16 Aug
  • Re: Why does this failScott Noyes16 Aug
  • Re: Why does this failSGreen16 Aug
    • RE: Why does this failAndrew Hargreaves16 Aug
      • inf and -inf valuesDaniel16 Aug
RE: Why does this failJay Blanchard16 Aug