Hi,

> Will the following two queries give the same results? Is there a beneifit to
> doing it either way?

1)
SELECT
SUM(Y.TOTAL)
FROM
TABLEONE X
LEFT OUTER JOIN TABLETWO Y ON
(X.LINK = Y.LINK AND Y.DELETED = 'N')
WHERE
X.LINK = 1

2)
SELECT
SUM(Y.TOTAL)
FROM
TABLEONE X
LEFT OUTER JOIN TABLETWO Y ON
(X.LINK = Y.LINK)
WHERE
X.LINK = 1 AND
Y.DELETED = 'N'

The results will be different because the first query will return probably NULL
and the second a value. Note that a condition in the WHERE clause is performed
after the joining and the ON-clause while joining. If you put an filter (except
checking on NULL) in the WHERE clause on a OUTER JOIN then it's in fact an INNER
JOIN (only for Y in this query). Thus the second query can be rewritten as an
INNER JOIN :

SELECT
SUM(Y.TOTAL)
FROM
TABLEONE X
JOIN TABLETWO Y ON (X.LINK = Y.LINK)
WHERE
X.LINK = 1 AND
Y.DELETED = 'N'

The advantage of this is that the optimizer can calculate in which order the
tables should be joined.

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