An interesting thing came up in the support list today, regarding explicit
join semantics. Since both Ann and Arno seemed to be "coming from the same
place" with respect to this issue, I'd be interested in their expanding on
this question. If it makes sense to me, I'd like to write a FAQ sheet or
similar with regard to it, since it news to me.

The inquirer wanted to know how to write this query:

select * from TABLE1 a
left join (
select * from TABLE2 a2 where a2."Val" = :Val
and a2."Pos" = :Pos ) b
on a."ID" = b."ID_ref"

He seemed to be attempting to make derived table style of query. I replied
as follows:

Well, the derived table seems redundant for this example, which appears to
be a straightforward left join.

select a.*, a2.*
from TABLE1 a
left join TABLE2 a2
on a."ID" = a2."ID_ref"
where a2."Val" = :Val
and a2."Pos" = :Pos

Next, Ann jumped in with this:

My understanding is that putting the conditions

a2."Val" = :Val and a2."Pos" = :Pos

into the where clause changes the query from a left join to an inner join,
since missing values from the right table won't pass the where sieve.

Putting the conditions into the "on" clause should give the expected result
for a left join.

select a.*, a2.*
from TABLE1 a
left join TABLE2 a2
on a."ID" = a2."ID_ref" and
a2."Val" = :Val and
a2."Pos" = :Pos

Arno confirmed this:

I think it's late or very early for Helen ;-)

The where clause should be in the on clause

SELECT
*
FROM
TABLE1 a
LEFT JOIN TABLE2 a2 ON (a."ID" = a2."ID_ref" and
a2."Val" = :Val and a2."Pos" = :Pos)

OK. Now, ever since we had explicit join support in IB (5.1?) I've
believed that

1) the semantics were identical whether the search criteria were in the ON
clause or the WHERE clause

AND

2) it's not good practice to merge search conditions with join conditions

Now, it's obvious that I'm misinformed about the semantics, at least as far
as Firebird is concerned. Both responses imply that WHERE conditions that
are ANDed to an ON condition are handled in an intermediate stream in some
way that affects the output.

This is interesting to me for at least two reasons: first, in The Book, in
the firm belief that the engine sifts out WHERE criteria that are ANDed to
the join criteria and defers them to the WHERE clause, I've recommended NOT
trying to combine search criteria with join criteria in this fashion,
specifically because some abstraction layers have trouble parsing out
parameters from such constructions. Secondly, if the semantics really are
different it throws fresh possibilities into solving a problem one of my
customers has currently with Firebird 1.0.

For this discussion, I'd like Ann or Arno (or, better, both) to explain
these semantics according to how the engine/optimizer actually processes
the request.

I'd also like to know at what point the semantics changed. Was this a Fb
1.5 thing, or was it already done by the time we had Fb 1.0?

cheers,
Helen