Archives of the TeradataForum

Message Posted: Mon, 30 Oct 2000 @ 15:28:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Note that this is also confusing because of the mix in join syntax usage. The parser correctly showed a problem with "search" criteria. My understanding is that a search clause can be applied before the join, while a predicate in a where clause must be applied after the join. I have not had time to verify this under V2R4, but we had issues with this under V2R3. When you use an outer join with a where clause, the predicate constraints are applied after the joins - basically handcuffing the optimizer.

The place to use this functionality is when testing for the outer join rows - in a count of NULLs or such.

From a style prespective, to keep yourself out of trouble like this, use either the Where join clauses or the Join/On syntax for all tables. Hence, when using an outer join - convert the entire query to Join/On.

     Ex 1:
     From T1, T2, T3
     Where T1.C1 = T2.C1
       and T2.C1 = T3.C1
       and T1.C2 = 'X'
       and T3.C3 = 'Y'

     Should be:

     Ex: 2
     From T1
     Inner Join T2
     on T1.C1 = T2.C1
     and T1.C2 <> 'X'
     Inner Join T3
     on T2.C1 = T3.C1
     and T3.C3 <> 'Y'

Do NOT do the following, unless you need to test the RESULT of the join.

     Ex: 3
     From T1
     Inner Join T2
     on T1.C1 = T2.C1
     LEFT Join T3
     on T2.C1 = T3.C1
     WHERE T3.C3 <> 'Y'
       and T1.C2 <> 'X'

In ex: 3, the system *could* migrate the T1.C2 constraint as a search (though I do not think it does), but not the T3.C3, which must be processed after the outer join. In Ex 2:, the system will apply both search conditions before doing the join.

The rule of thumb that I use is: Unless you specifically need to test a constraint AFTER a join - ALWAYS code using a search condition.

The other rule of thumb: always code using SQL92 syntax - it appears that the optimizer, in general, does a more predictable job.

There are other examples of this problem in the list archives.

Michael McIntire

	<Prev	Next>		<<First	<Prev	Next>	Last>>