We are upgrading from 6.1b to 6.5. We have noticed that the SQL is being written differently when the query includes a self join. In 6.1b it adds the statement to the WHERE clause, in 6.5 it adds the statement to the FROM clause. I think it is in the .prm file, but I can’t figure out what is different, any ideas???
gzollinger1 (BOB member since 2003-01-23)
Could it be that you are using ANSI92 SQL syntax, a universe parameter?
Andreas 
(BOB member since 2002-06-20)
Just want to extend this question. What difference does it make if the join condition is in FROM or WHERE clause 
Specially with respect to performance.
Is it better to use stubby join(self join) or a complex join in which the stubby join condition on a table is specified in that table’s join with other tables
salam 
(BOB member since 2005-01-27)
Just to clarify: Are you referring to a self-join, which is a table joined to an alias of itself, or a self-restricting join, which a stub join used to apply limits on a specific table?
MichaelWelter 
(BOB member since 2002-08-08)
Let me clarify. Suppose that there is table A which has a attributes/columns a1,a2,a3 etc. Similarly there is a table B which has attributes/columns b1,b2,b3 etc. These two table are to be joined on a1 and b1. Further from table A I should only pick those records for which a2=“1”
Now there can be two options for me.
A.a1=B.b1 and A.a2="1"
A.a1=B.b1
additional self restricting join on table A with condition a2="1"
In these two different scenarios the query generated is different. In first case the condition on A.a2 appears in the FROM clause whereas in the second case the condition appears in the WHERE clause.
I wanted to know the pros and cons of two approaches from the prespective of performance.
salam 
(BOB member since 2005-01-27)
Yep, apparently 6.5 handles that .prm file differently between 6.1b and 6.5, and we missed it. Thanks!
gzollinger1 (BOB member since 2003-01-23)
This might help (or not):
ADAPT00754142
Description:
Self-join conditions that are used in query may fail or may reduce performance.
The cause of the problem is that self-join conditions were generated within the WHERE clause of the query by default.
New Behavior:
This problem is resolved.
To solve the problem, previous changes made in response to another issue (ADAPT641525) have been reversed: instead of
generating self-joins in the WHERE clause, by default self-joins will be generated in the FROM clause. However, users have
the option to generate self-joins in the WHERE clause by using this new parameter: "SELFJOINS_IN_WHERE=yes.
Italo Sessegolo (BOB member since 2002-08-29)