Re: SQL Anomaly?

}From: spitz@GANS2X.ana.med.uni-muenchen.de (Richard Spitz) }Date: 31 Jan 1996 09:55:44 GMT }X-Informix-List-Id: <news.20751> } }Jonathan Leffler (johnl@informix.com) wrote: }: What you had was actually a horrible correlated sub-query... } }: DELETE FROM tmp_kontakt }: WHERE s_doknr IN (SELECT UNIQUE(tmp_kontakt.s_doknr) FROM kontakt) } }: Note the extra table specifier. }: [...] }: It was a correctly formatted subquery, but not the one you thought it was. } }Thanks for the info! It sure was an eye-opener for me! } }What would have happened if "s_doknr" (and not "ko_s_doknr") had indeed been }the column name in table "kontakt"? Would it have returned the correct }result, or would the same correlated subquery have resulted? The original query said "UNIQUE(s_doknr)". If S_DokNr was a column in the Kontakt table, then because the column name is part of the table in sub-SELECT, it would have been interpreted as Kontakt.S_DokNr. It was only when the S_DokNr column was not found in Kontakt that the parser attempted to find it in the outer Tmp_Kontakt table and converted the query into a correlated subquery. May I also comment that "SET EXPLAIN ON" would have told you what was going on... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>