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
>
>