Re: Join questions
On Fri, 19 Sep 2003 14:08:12 -0400, Rich wrote:
> I have a couple fairly basic join questions I am hoping someone can answer. I
> am familiar with other DB's but am new to Informix. Here are two queries
> which I am trying to understand...
>
> 1) select ... from table1, table2, outer table3, outer table4 where ...
>
> Is the outer join a left or right outer join? Since there is no "on" clause,
> how does Informix join the tables?
All Informix OUTER joins are LEFT OUTER JOINs. Informix's optimizer will infer
the ON conditions from the join filters in the WHERE clause which must appear
to avoid a Cartesian Product.
> 2) select ... from table1, inner loop join table2 on <condition> inner loop
> join table3 on <condition> left outer join table4 on <condition> left outer
> join table3 on <condition>
>
> What is the difference between an inner join and an inner loop join? How do
> the inner loop joins and left outer joins interact?
This is an implementation specific clause. Informix is capable of several
types of joins: nested loop join, hash-join, temporary index join(rare), and
others. The format selected is dependent on the engine's current OPTCOMPIND
setting in the ONCONFIG file and the results of the optimizer's calculations
based on the Data Distributions generated by UPDATE STATISTICS <MEDIUM|HIGH>
... The optimizer is free to select the most efficient join method from its
quiver unless you specify a join method using optimizer directives (ex: {+
AVOID_HASH} or {+AVOID_NL}). Even if you do specify a join method to use or to
avoid the optimizer may override your choice if its choice is sufficiently
better.
Note that when using the Informix syntax for OUTER joins the WHERE clause
filters are ALL applied pre-join. Using ANSI syntax the ON clause filters are
applied pre-join and the WHERE clause filters are applied post-join. The main
difference is that using ANSI syntax you can:
select *
from one_table left outer join table_two
ON one_table.key = table_two.key
WHERE table_two.some_column IS NULL;
To select rows from one_table which are NOT found in table_two (or inverting
the WHERE clause those which ARE found). Using Informix syntax you must either
use a sub_query or select the outer joined rows to a temp table and select from
the temp table WHERE some column from the outer table IS NULL or IS NOT NULL.
Art S. Kagel