Yes, the sub-query return some null-value. I thought that the 2 queries were equivalent... Hum, i think i need to reopen my SQL book ! Thank Elke
Frédéric. -----Message d'origine----- De : Zabach, Elke [mailto:[EMAIL PROTECTED] Envoyé : mercredi 6 juillet 2005 09:00 À : Frédéric Demilly; [email protected] Objet : AW: Strange result with subqueries Frédéric Demilly wrote: > > Hello all, > I've a strange result with sub queries. > Here the tables i use: > CREATE TABLE "TORPEDO"."EMPLACEMENT" > ( > "EMP_C_CODE" Char (5) ASCII NOT NULL, > "EMP_C_LIBELLE" Varchar (25) ASCII, > "EMP_L_ACTIF" Boolean DEFAULT FALSE, > "EMP_L_MODIF" Boolean DEFAULT FALSE, > PRIMARY KEY ("EMP_C_CODE") > ) > CREATE TABLE "TORPEDO"."EMP_ART" > ( > "ART_C_REF" Char (12) ASCII NOT NULL, > "STO_C_CODE" Char (5) ASCII, > "SKU_C_CODE" Char (17) ASCII, > "SKU_C_CAB" Char (13) ASCII, > "EMP_C_CODE" Char (5) ASCII, > "EA_I_TYPE" Integer DEFAULT 0, > "ETP_C_CODE" Char (5) ASCII, > CONSTRAINT "TYPE D'ECLATEMENT" CHECK "EA_I_TYPE" IN (0,1,2), > FOREIGN KEY "REFERENCE" ("ART_C_REF") REFERENCES > "TORPEDO"."ARTICLE" ("ART_C_REF") ON DELETE CASCADE, > FOREIGN KEY "CODE STOCK" ("STO_C_CODE") REFERENCES > "TORPEDO"."STOCK" ("STO_C_CODE") ON DELETE CASCADE, > FOREIGN KEY "CODE EMPLACEMENT" ("EMP_C_CODE") REFERENCES > "TORPEDO"."EMPLACEMENT" ("EMP_C_CODE") ON DELETE CASCADE, > FOREIGN KEY "CODE ETAPE" ("ETP_C_CODE") REFERENCES > "TORPEDO"."ETAPE" ("ETP_C_CODE") ON DELETE RESTRICT > ) > > And the indexes for both table: > CREATE INDEX "EMP_ACTIF" ON "TORPEDO"."EMPLACEMENT"("EMP_L_ACTIF" ASC) > > CREATE UNIQUE INDEX "PRIMAIRE" ON "TORPEDO"."EMP_ART"("ART_C_REF" ASC, > "STO_C_CODE" ASC, "SKU_C_CODE" ASC) > CREATE INDEX "SECONDAIRE" ON "TORPEDO"."EMP_ART"("EMP_C_CODE" ASC, > "STO_C_CODE" ASC) > > If i run this query, i get many results: > select emp_c_code,emp_c_libelle from emplacement where emp_l_actif and > not exists (select emp_c_code from emp_art where sto_c_code = '1CEN' and > emplacement.emp_c_code = emp_art.emp_c_code) > > But if i run this other one, which should return the same result, i > didn't get anything: > select emp_c_code,emp_c_libelle from emplacement where emp_l_actif and > emp_c_code not in (select emp_c_code from emp_art where sto_c_code = > '1CEN') > > None of the indexes are marked as bad, i've just updated statictics on > both table. > > Any idea ? I assume that select emp_c_code from emp_art where sto_c_code = '1CEN' results in at least one NULL-value for emp_c_code. This would cause the difference. Elke SAP Labs Berlin > > TIA. > > Frédéric > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
