I don't know whether there is a difference or not for the optimizer -
all I can say is that I've never noticed any. The examples I showed
would give a different result set, but for [INNER] JOINs, I've never
noticed a difference in the chosen plan due to whether the criteria were
in the WHERE or JOIN clause.

Though this answer is only from my experience, and I have never really
tried to see whether there was a difference or not (I've just always
done as Dimitry suggest and put join criteria in the JOIN clause and
where criteria in the WHERE clause, the exception to this is queries
with outer joins where the results may differ depending on whether
criteria is put in the WHERE or JOIN clause). Hopefully, if there is a
difference, people on this list with such experience would jump in and
teach us.

Set

Tetram Corp wrote:

> Thanks for explanation.
> So, if I understand, for optimizer, the only thing it change between a
> join criteria and a where criteria is priority when it makes the plan.
> So, in most cases, it doesn't matter where the criteria is, but in some
> (rare and specific?) cases, it could help FB to put some of them in join
> instead of where.
>
> Is it correct ?
>
> Thierry
>
> Svein Erling Tysvær a écrit :
>>
>> 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%40yahoogroups.com>
>> [mailto:firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.com>] On Behalf Of Tetram Corp
>> Sent: 15. juni 2009 10:27
>> To: firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.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