| Subject | Re: inner join with additional conditions |
|---|---|
| Author | Adam |
| Post date | 2007-09-17T03:02:17Z |
--- In firebird-support@yahoogroups.com, "bill2janesoft@..."
<bill2janesoft@...> wrote:
The optimiser is (usually) clever enough to internally come up with
the same plan, so it mostly comes down to readability.
This is what I do (that you are free to take or ignore ;).
Conditions relating to the rules of what defines the relationship
between t1 and t2 should all be inside the join condition. Any other
conditions go inside the where clause. This keeps the boring 'sticky
tape' details out of the where clause logic. It also means that if
someone drops the join to t2 sometime in the future, they don't forget
about the t1.b>0 condition.
Also keep in mind that you can only interchange them if your query is
an inner join. If you use an outer join, the two queries you give may
return different datasets.
The outer join (below) will cause records existing in t2 not in t1 as
having t1.b of null. Your where clause explicitly eliminates these records
select ... from t1 full outer join t2 on t1.a=t2.a where t1.b>0
But the following query keeps those records but with a null value.
select ... from t1 full outer join t2 on t1.a=t2.a and t1.b>0
Now I have told you to always do it one way, there may be a few
performance hacks where you may have to break this rule to get a
decent plan. By moving an expression between the join condition and
where clause in a complex query, it may assist the optimiser. But this
is the exception rather than the rule, and I have never seen it on
such a simple query.
I tend to avoid composite primary keys (using a surrogate field), so
my join conditions mostly have just one expression. I only use those
expressions based on the values in one of the tables (like t1.b>0) if
my join is some sort of outer join where I want to keep rows where
there is no match rather than eliminate them.
Adam
<bill2janesoft@...> wrote:
>eg the
> AFAIK additional conditions for inner join can be placed at the end,
> following 2 queries should produce the same resultHi Bill,
>
> select ... from t1 inner join t2 on t1.a=t2.a where t1.b>0
>
> select ... from t1 inner join t2 on t1.a=t2.a and t1.b>0
>
> Under what circumstance that one form will be better than the other?
>
The optimiser is (usually) clever enough to internally come up with
the same plan, so it mostly comes down to readability.
This is what I do (that you are free to take or ignore ;).
Conditions relating to the rules of what defines the relationship
between t1 and t2 should all be inside the join condition. Any other
conditions go inside the where clause. This keeps the boring 'sticky
tape' details out of the where clause logic. It also means that if
someone drops the join to t2 sometime in the future, they don't forget
about the t1.b>0 condition.
Also keep in mind that you can only interchange them if your query is
an inner join. If you use an outer join, the two queries you give may
return different datasets.
The outer join (below) will cause records existing in t2 not in t1 as
having t1.b of null. Your where clause explicitly eliminates these records
select ... from t1 full outer join t2 on t1.a=t2.a where t1.b>0
But the following query keeps those records but with a null value.
select ... from t1 full outer join t2 on t1.a=t2.a and t1.b>0
Now I have told you to always do it one way, there may be a few
performance hacks where you may have to break this rule to get a
decent plan. By moving an expression between the join condition and
where clause in a complex query, it may assist the optimiser. But this
is the exception rather than the rule, and I have never seen it on
such a simple query.
I tend to avoid composite primary keys (using a surrogate field), so
my join conditions mostly have just one expression. I only use those
expressions based on the values in one of the tables (like t1.b>0) if
my join is some sort of outer join where I want to keep rows where
there is no match rather than eliminate them.
Adam