Subject Re: [firebird-support] Re: detailed join mechanism description (FB 1.5)
Author Svein Erling Tysvaer
radekok wrote:
>> You unfortunately can't define the join order through brackets
>> if you want to left join to the inner join two tables, although that
>> can be achieved with a view of the inner join.
> Is the view the only way of affecting a join order by a SQL programmer
> against FB 1.5?

No, of course not. Though we normally don't use the term 'join order',
we talk about PLANs. Firebird may use multiple indexes against any
particular table and you have to look at the individual indexes rather
than simply the order of the tables to know whether the plan is a good
one. Theoretically, you may write the PLAN in your sql statement and
have full control over how the select is executed. But that is in
general not recommended (it is simply a too static and cumbersome
approach), and I do not know of anyone actually doing that. Those of us
that like sql puzzles, normally approach this a bit differently.

First, I write my sql statements and then prepare them. Then I look at
the plan, see if the optimizer has chosen sensible indexes to use - and
if I disagree with its choice, then I see if I can possibly hint the
optimizer to choose a different plan. If so, I normally prevent the
optimizer from using certain indexes through adding '+0' or similar.
Though the optimizer is - in general - good a choosing PLANs and more
often than not, no alteration is required. Certain tasks may be tricky
to do through normal sql, and using a stored procedure may make the task
much easier to achieve.

The only general advice to give when writing sql statements, is to stick
to [inner] JOINs whenever possible and use LEFT [outer] JOIN only when
required (and if I use such joins, then I put them at the end of the
select statement). FULL [outer] JOIN I've hardly ever used, and they
ought to be used very carefully.