Where it really matters, is when used with outer joins:

SELECT *
FROM a
LEFT JOIN b ON a.a = b.a AND b.b = 'hello'

will return all records from A as well as content of matching records from B if B.B = 'hello', whereas

SELECT *
FROM a
LEFT JOIN b ON a.a = b.a
WHERE b.b = 'hello'

only returns records where B.B = 'hello', i.e. records from A without a matching B are eliminated. This last query isn't really a LEFT JOIN, and could be better written like this:

SELECT *
FROM a
INNER JOIN b ON a.a = b.a
WHERE b.b = 'hello'

With inner joins, the results of putting the criteria in the JOIN or WHERE clause ought to be identical, but Dimitrys advice is good - it is easier to read a query that puts the join and where criteria in the logically correct places. I have not heard about performance issues from putting things in the wrong clause (I would expect the plan to be identical, but check for yourself - I haven't tested), that is with the exception that changing from INNER JOIN to LEFT JOIN sometimes can be used as an alternative to +0 or ||'' when the optimizer makes a suboptimal choice and then it is important to have something in the WHERE clause to avoid an incorrect result (e.g. if you had the last of the above examples and wanted to force A to be used before B in the plan, then WHERE b.b = 'hello' is superior to putting b.b in the LEFT JOIN clause).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Tetram Corp
Sent: 15. juni 2009 10:27
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Theory about join vs where

Hi,

the current thread about join remains me a question I already have, and
of course no real answer at this time.

Are there rules about join and where ?
Speaking of efficiency, what is better:
- put as lot as possible criteria in join clause and let where clause
with those which cannot be put in join
- put as lot as possible criteria in where clause and let join clause
with only fk
- it depends : some cases are better with first solutions, some are
better with second one ?

With another POV, are there some clauses we should always put in join
clauses ? are there some clauses we should never put in join clauses ?

(We will suppose our tables are correctly indexed: on criteria fields
and fk)

Thierry