Archives of the TeradataForum
Message Posted: Mon, 30 Oct 2000 @ 15:28:22 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|