Final Attempt: The Correct Answer - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-28
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Final Attempt: The Correct Answer

Example 4: Outer Join

Finally, we have the correct answer. This example is an outer join that provides the desired answer to the original business question.

     SELECT c.custnum, b.monthly_revenue
     FROM sampdb.customer AS c
     LEFT OUTER JOIN sampdb2.revenue AS b ON c.custnum = b.custnum
     AND   b.data_year_month = 199707
     WHERE c.district ='K'
     AND  (c.service_type = 'ABC' 
     OR    c.service_type = 'XYZ')
     ORDER BY 1;

This query returns 18,034 rows.

Brief Analysis of the Result

The cardinality of this query result, 18,034 rows, reconciles with the expected number of returned rows (see “Single Table Cardinality Estimate” on page 284).

13,010 rows have values (non-nulls) for monthly_revenue.

EXPLAIN Text for Example 4

The following EXPLAIN text is reported on the query in “Example 4: Outer Join” on this page. Notice the bold text indicating that the system performed a left outer join:

Explanation
-----------------------------------------------------------------------
   1) First, we lock a distinct SAMPDB."pseudo table" for
      read on a RowHash to prevent global deadlock for SAMPDB.b.
   2) Next, we lock a distinct SAMPDB."pseudo table" for
      read on a RowHash to prevent global deadlock for SAMPDB.c.
   3) We lock SAMPDB.b for read, and we lock SAMPDB.c for read.
   4) We do an all-AMPs JOIN step from SAMPDB.c by way of a
      RowHash match scan with a condition of (
      "((SAMPDB.c.service_type = 'abc') OR
      (SAMPDB.c.service_type = 'xyz')) AND
      (SAMPDB.c.district = 'k')"), which is joined to
      SAMPDB.b by way of a RowHash match scan with a
      condition of ("SAMPDB.b.data_year_month = 199707.00").
      SAMPDB.c and SAMPDB.b are left outer joined using a merge join, 
      with a join condition of ("SAMPDB.c.custnum = SAMPDB.b.custnum").
      The result goes into Spool 1 (group_amps), which is built locally
      on the AMPs.  Then we do a SORT to order Spool 1 by the sort key
      in spool field1.  The size of Spool 1 is estimated to be
      18,034 rows.  The estimated time for this step is 0.07
      seconds.
   5) Finally, we send out an END TRANSACTION step to all AMPs involved
      in processing the request.
   -> The contents of Spool 1 are sent back to the user as the result of
      statement 1.  The total estimated time is 6 minutes and 2 seconds.

Analysis of the EXPLAIN Text

1 The Lock Manager applies the appropriate READ locks for the query.

2 The Optimizer selects the best join algorithm for this outer join (a Merge Join) and applies the conditions from the WHERE and ON clauses, respectively.

The left (outer) table is limited by the search conditions in the WHERE clause, and the search condition in the ON clause for the right (inner) table defines the nullable nonmatching rows.

The EXPLAIN text confirms that this is a true outer join (“...SAMPDB.c and SAMPDB2.b are left outer joined...”).

3 The result of the query, 18,034 rows, is returned to the requestor.

This is the correct answer to our business question.