Archives of the TeradataForum

Message Posted: Mon, 06 Oct 2003 @ 09:16:35 GMT

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

The join can be written in two ways:

SELECT
        TabA.ac1, TabA.ac2
FROM
        ((
        A AS TabA
LEFT OUTER JOIN
        B AS TabB
ON
        TabA.ac1 = TabB.bc1
        )
LEFT OUTER JOIN
        C AS TabC
ON
        TabA.ac1 = TabC.cc1
        )
WHERE
        TabB.bc2 > 10
AND     TabC.cc2 = 11
;

SELECT
        TabA.ac1, TabA.ac2
FROM
        ((
        A AS TabA
LEFT OUTER JOIN
        B AS TabB
ON
        TabA.ac1 = TabB.bc1
AND     TabB.bc2 > 10
        )
LEFT OUTER JOIN
        C AS TabC
ON
        TabA.ac1 = TabC.cc1
AND     TabC.cc2 = 11
        )
;

The difference in both SQL is that, one will select the rows selection in spool will also include the condition TabB.bc2 > 10 and TabC.cc2 = 11, that is, the spool for TabB it will contain the rows that fulfill the join condition and TabB.bc2 > 10 and for TabC it will contain rows that fulfill the join condition as well as the second condition TabC.cc2 = 11. While for the other SQL, it will fetch the complete tables in spool and will apply join on the spool and then eliminate the rows that fulfil the secondary conditions.

It is always a good idea (specifically for Teradata RDBMS) that you should write the most efficient and optimized SQL. You can verify / compare both the SQL by looking at the explain statement. This way you would be able to find which SQL is more optimized on your system and thus will allow you to adopt the efficient SQL.

Regards,

Muhammad Altaf
Professional Services Consultant
Teradata Certified Master
Teradata, A Division Of NCR

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