mercredi 24 décembre 2014

Best practice - use of derived table or use the original table in join?


Imagine I have the following tables in Teradata :



TableA : Field1|Field2|Field3
TableB : Field1|Field4|Field5


I am trying to join these two tables


Method 1 :



SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN TableB
ON TableA.Field1 = TableB.Field1

WHERE TableB.Field5<>0


Method 2 :



SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN TableB
ON TableA.Field1 = TableB.Field1
AND TableB.Field5<>0


Method 3 :



SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN (
SEL Field1 ,Field4
FROM TableB
WHERE Field5<>0
) DTable
ON TableA.Field1 = DTable.Field1


Question : I always go for Method 3. But recently my colleague advised it is not the best coding practice as it is using the subquery in join. Is there anything like a coding standard for joins ?





Aucun commentaire:

Enregistrer un commentaire