jvelardeverdin wrote:

> Hi Kjell,
> I reproduced something similar to your query:
>
> =======================================
> Query 1:
> select COUNT(*)
> from SEQNUM_LOG
> WHERE EXISTS(
> SELECT 1 FROM SEQNUM WHERE SEQNUM_LOG.IDSEQNUM = SEQNUM.ID)
>
> This query returned a value.
>
> =======================================
> Query 2:
> select COUNT(*)
> from SEQNUM_LOG
> WHERE SEQNUM_LOG.ID = ( SELECT ID FROM SEQNUM)
>
> This query doesn´t return any value, so, in my opinion, the Q1
> isn't
> the same the Q2.
>
> =======================================

Thanks Julio for your tests. I think you mistyped the queries though.

In query 1 the where condition whould read:

WHERE NOT EXISTS(
SELECT 1 FROM SEQNUM WHERE SEQNUM_LOG.ID = SEQNUM.ID)

In query 2 the where condition whould read:

WHERE SEQNUM_LOG.ID NOT IN ( SELECT ID FROM SEQNUM)

Could you please try again? My problem is that Q1 returns a count > 0
but Q2 doesn't (this is actually on SQL Server 2000 - haven't tried it
on FB). IMO both queries should return the same count, i.e. the count of
records in SEQNUM_LOG whose ID doesn't appear in SEQNUM.ID. (Would that
be zero in your example? I'd assume so from the table names.)

> Query 3:
> SELECT
> COUNT(*) AS FIELD_1
> FROM
> SEQNUM
> INNER JOIN SEQNUM_LOG ON (SEQNUM.ID=SEQNUM_LOG.IDSEQNUM)

Again, not the query I'm after but I could do this:

SELECT
COUNT(*)
FROM
SEQNUM_LOG
LEFT JOIN SEQNUM ON (SEQNUM.ID = SEQNUM_LOG.ID)
WHERE SEQNUM.ID IS NULL

Performance isn't really an issue since it's a one-off. I'm concerned
about correct results, which is absolutely vital.

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64