Hi Helen,

> An interesting thing came up in the support list today, regarding explicit
> join semantics. Since both Ann and Arno seemed to be "coming from the same
> place" with respect to this issue, I'd be interested in their expanding on
> this question. If it makes sense to me, I'd like to write a FAQ sheet or
> similar with regard to it, since it news to me.

<snip>

I'll try to explain as how i see it:

SELECT
*
FROM
Table1 t1
JOIN Table2 t2 ON (1 = 1)
WHERE
t2.ID = t1.ID

"Rules":
The ON-clause determine the relationship between the inner stream (this case t2)
and the outer streams (this case t1) and is applied after processing the inner
stream (t2).
The WHERE-clause is applied after all the streams (relations) in the FROM are
processed.

inner join:
While this is an inner join every WHERE-clause comparison can be processed the
same as an ON-clause. The optimizer will combine (and-ing) the ON-clause and the
WHERE-clause together so that it makes no difference (for the optimizer) if the
comparisons are inside the WHERE-clause or ON-clause. (Of course all streams
should be accessible before applying the comparison, but that's a task of the
optimizer to find out)

SELECT
*
FROM
Table1 t1
LEFT JOIN Table2 t2 ON (1 = 1)
WHERE
t2.ID = t1.ID

Using the "rules" explained above:
The JOIN order is here explicit (JOIN (T1, T2)) and the ON-clause is applied to
the inner stream (t2) before it is handled as outer join.
The FROM clause is processed first and this _could_ give NULLs for the inner
stream t2.
While the WHERE-clause is processed after, the comparison (t2.ID = t1.ID) will
fail (returning false) on records where the field IS NULL. This meant the OUTER
JOIN for this query can be seen the same as an INNER JOIN (because NULL results
are filtered away). The FB1.5 optimizer will try to combine the WHERE-clause and
ON-clause items when possible. But IS NULL conjunctions may not be used to
combine into the ON-clause.

SELECT
*
FROM
Table1 t1
LEFT JOIN Table2 t2 ON (1 = 1)
WHERE
t2.ID IS NULL

is not the same as

SELECT
*
FROM
Table1 t1
LEFT JOIN Table2 t2 ON (t2.ID IS NULL)


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://newsgroups.firebirdsql.info