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

Question

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 ?

Answer 1

Your colleague is right. Method 3's subquery can waste compute. Therefore use Method 2.

Method 1 and Method 3 do not yield the same results. The WHERE clause in Method 1 performs the filtering after joining the tables. In effect, it turns the LEFT JOIN into an INNER JOIN.

Method 2 is equivalent to Method 3 because the AND clause performs the filter on TableB before the join (which is what the derived query does).

I created a SQLFiddle which illustrates the case. Full code follows:

CREATE TABLE TableA(
    Field1 int
  , Field2 varchar(10)
  , Field3 varchar(10)
  );

CREATE TABLE TableB(
    Field1 int
  , Field4 varchar(10)
  , Field5 int);

INSERT INTO TableA
SELECT 0, 'Zero', 'Inner'
UNION ALL
SELECT 1, 'One', 'Inner'
UNION ALL
SELECT 2, 'Two', 'Left';

INSERT INTO TableB
SELECT 0, 'Zero', 0
UNION ALL
SELECT 1, 'One', 1
UNION ALL
SELECT 5, 'Five', 5;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , TableB.Field4
  , TableB.Field5
INTO Method1
FROM TableA
  LEFT JOIN TableB on TableA.Field1 = TableB.Field1
WHERE TableB.Field5 <> 0;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , TableB.Field4
  , TableB.Field5
INTO Method2
FROM TableA
  LEFT JOIN TableB on TableA.Field1 = TableB.Field1
    AND TableB.Field5 <> 0;

SELECT TableA.Field1
  , TableA.Field2
  , TableA.Field3
  , DTable.Field4
  , DTable.Field5
INTO Method3
FROM TableA
  LEFT JOIN (SELECT *
             FROM TableB
             WHERE TableB.Field5 <> 0) DTable
  on TableA.Field1 = DTable.Field1;

SELECT *
FROM Method1;

SELECT *
FROM Method2;

SELECT *
FROM Method3;

Answer 2

Teradata's optimizer quite possibly will re-write Method 3 to the same query plan as Method 2. Method 1 will result in an INNER JOIN because the qualification on the RIGHT table shouldn't be in the WHERE clause but the ON clause of the OUTER JOIN condition. If you were to place an aggregate step such as a DISTINCT or GROUP BY in the derived table of Method 3 you will find the optimizer will likely satisfy the derived table as an individual step without re-writing the plan.

I would suggest that you run the EXPLAIN for each query and compare the output.