Change to

SELECT COUNT(distinct(c.codpostal))
FROM CUBOLABO c WHERE c.CODPOSTAL <> 0
AND NOT EXISTS( SELECT * FROM DETCP d WHERE d.intcabzonas = 2 and c.codpostal = d.codpostal)

and I guess you'll be more satisfied by the result. Your original statement would require DETCP to be an empty table to return a number higher than 0.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Eduardo Lopez
Sent: 25. mars 2008 16:13
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Differents results using EXISTS

I'm trying to optimize a query using EXISTS, but it seems that i'm doing something wrong:

Server: Pentium IV 3Ghz. 512MB ram
SO: Ubuntu server 7.10
DB server: Firebird CS 2.1 RC2 (17755)

The following query:

SELECT COUNT(distinct(codpostal))
FROM CUBOLABO WHERE CODPOSTAL <> 0
AND codpostal not in ( SELECT distinct(codpostal) FROM DETCP WHERE intcabzonas = 2)

Starting transaction...
Preparing query: SELECT COUNT(distinct(codpostal))
FROM CUBOLABO WHERE CODPOSTAL <> 0
AND codpostal not in ( SELECT distinct(codpostal) FROM DETCP WHERE intcabzonas = 2)
Prepare time: 00:00:00.
PLAN SORT ((DETCP INDEX (CPUNICOENZONA)))
PLAN (CUBOLABO NATURAL)

Executing...
Done.
39585217 fetches, 0 marks, 40024 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 15363036 index, 2194830 seq.
Delta memory: 39776 bytes.
Execute time: 00:01:46.

Returns 1360 (the correct value)


If I change to:

SELECT COUNT(distinct(codpostal))
FROM CUBOLABO WHERE CODPOSTAL <> 0
AND NOT EXISTS( SELECT distinct(codpostal) FROM DETCP WHERE intcabzonas = 2)


Executing...
Done.
39585217 fetches, 0 marks, 40024 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 15363036 index, 2194830 seq.
Delta memory: 39776 bytes.
Execute time: 00:01:46.
Prepare time: 00:00:00.
PLAN SORT ((DETCP INDEX (CPUNICOENZONA)))
PLAN (CUBOLABO NATURAL)


Executing...
Done.
4469585 fetches, 0 marks, 39994 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 10 index, 2194830 seq.
Delta memory: -1112 bytes.
Execute time: 00:00:04.

But returns 0.

Thanks in advance.
Eduardo