Tim

I have the following SQL statement:

1) select cod,nom,cpf,cpj,cpx,hfor.raz from hcoo
inner join hfor on (hcoo.cpf=hfor.cpf) nojoinoptimize

and
2) select cod,nom,cpf,cpj,cpx,hfor.raz from hcoo
inner join hfor on (hcoo.cpf=hfor.cpf and hcoo.cpf<>null and hfor.cpf<>null)
nojoinoptimize

Both tables (hcoo and hfor) have indexes on cpf field (string).
Both tables have many null entries in the cpf field.

hfor = 4940 records (4325 with cpf=null)
hcoo = 10627 records (5429 with cpf=null)

result set = 531 records

When I execute the statement 1) it hangs my application (I've cancelled
after 2 minutes) and statement 2) gives me the right results in 2,4 seconds.

I think DBISAM should ignore null values internally or am I wrong ? BTW,
even with nulls the result time on both should be almost the same.

Eduardo

"Jose Eduardo Helminsky" <contato@hpro.com.br> wrote in
news:46C57D3A-45A4-444D-A3AB-47B34414B81B@news.elevatesoft.com:

Jose,

> 2) select cod,nom,cpf,cpj,cpx,hfor.raz from hcoo
> inner join hfor on (hcoo.cpf=hfor.cpf and hcoo.cpf<>null and
> hfor.cpf<>null) nojoinoptimize
>

You should limit the ON part to fields involving the JOINed table, which
automatically removes the need to specify AND hfor.cpf<>null:

2) SELECT cod,nom,cpf,cpj,cpx,hfor.raz
  FROM hcoo
  INNER JOIN hfor ON hcoo.cpf=hfor.cpf
WHERE hcoo.cpf<>null
nojoinoptimize
--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Eduardo,

<< I think DBISAM should ignore null values internally or am I wrong ? >>

DBISAM deviates from the SQL standard by allowing Column=NULL comparisons,
and this holds true for JOINs also.

<< BTW, even with nulls the result time on both should be almost the same.
>>

No, the first one is going to generate over 23 million rows in the result
set due to the matching NULLs.   The second one won't because you're
filtering out the NULLs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

> No, the first one is going to generate over 23 million rows in the result
> set due to the matching NULLs.   The second one won't because you're
> filtering out the NULLs.

Got it.

BTW, how ElevateDB handle this situation ? This deviate from SQL standards
too ?

Thanks

Eduardo

Eduardo,

<< BTW, how ElevateDB handle this situation ? This deviate from SQL
standards too ? >>

Nope.  It follows the SQL 2003 standard closely and does not match NULLs for
the joins.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

I suspect this but....and it is very good.

Thanks again

Eduardo