--- In firebird-support@yahoogroups.com, "radekok" <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, we are talking about two different things. You are talking about
the PLAN order, or if you like the order the tables are considered
when executing the query. Different plans make NO DIFFERENCE to the
results returned, except the order may be different in the absense of
an order by clause.

Brackets make no difference to the plan that is used to solve a given
query. Set has provided some useful information about how he optimises
queries, and he probably has the best ability I have seen to make
queries fly. Make sure the statistics are up to date and stick with
the +0 trick to force the elimination of 'dumb' indices. That will
help you derive the ideal plan.

What I was talking about was something like below:

imagine 3 tables:

a (id)
b (id, aid)
c (id, bid)

imagine a view:

create view d
(
aid,
bid,
cid
)
as
select b.aid, b.id, c.id
from b
join c on (b.id = c.bid);

It is very hard to replicate the query:

select *
from a
left join d on (a.id = d.aid)

using only a,b and c:

select *
from a
left join b on (a.id = b.aid)
left join c on (b.id = c.bid)

doesn't capture the inner join between b and c, while

select *
from a
left join b on (a.id = b.aid)
join c on (b.id = c.bid)

effectively forces b to become an inner join anyway. I thought you
were asking if you could use brackets to define join order in
something like:

select *
from a
left join (b on (a.id = b.aid) join c on (b.id = c.bid))

which is of course invalid syntax. The closest you can get under
Firebird 1.5 (apart from the view) is to use left joins, then to use a
where clause to enforce the inner join.

select *
from a
left join b on (a.id = b.aid)
left join c on (b.id = c.bid)
where b.id is null
or (b.id is not null and c.id is not null)

I haven't tested the above, and may have missed something, which is
probably the point I make, it is really SQL89 type conditions. One of
the great things about SQL92 join conditions was that it nicely
separated the logic of matching fields from the logic of deciding
which records are relevant.

I am pretty sure this is something that is easier solved with derived
tables in Firebird 2, although I am not yet familiar enough with the
syntax to give it a try.

Adam