Hi,

> Arno, looking at your query I would like to ask a question for my own
> information.

> What is the difference between
> SELECT
> DISTINCT
> pres.productid
> FROM
> presence pres
> JOIN product prod ON (pres.productid = prod.id)
> JOIN V_SYS_IDLISTS v ON (v.ID = pres.outletid)
> WHERE
> (pres.valuedate >= '2003-09-01') and (pres.valuedate < '2003-11-01')
>
> And
>
> SELECT
> DISTINCT
> pres.productid
> FROM
> presence pres
> ,product prod ON
> ,V_SYS_IDLISTS v ON
> WHERE
> (pres.productid = prod.id)
> And
> (v.ID = pres.outletid)
> and
> (pres.valuedate >= '2003-09-01') and (pres.valuedate < '2003-11-01')
>
>
> (not looking at the tables per se, just using them as examples for the
> difference in join syntax)

I guess the ON in the last query was a typo ;-)

The first query uses the SQL-92 syntax and the second SQL-89 syntax. They
return both exactly the same results, but i prefer the JOIN syntax because
among others it's much better readable. In a quick look i can see what
belongs together. Besides that the SQL-92 supports OUTER JOINs so you can
extend the first query very easy with a LEFT JOIN and also the optimizer can
do a better task in some circumstances.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81