Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 12 Feb 2005 @ 04:37:35 GMT

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

Subj:   Re: Guidelines for using Distinct vs. group by
From:   Michael Larkins

  A subquery itself can legitimately return one or more values.  

This is a true statement but only from the table(s) in the main query. However, if the subquery contains dups, the first would always be found when it uses an equivalent of an IN list. Therefore, a dup would take up needless space and therefore, has always been eliminated. In the old days a subquery aqctually did a DISTINCT operation in Teradata because this was the most efficient way to accomplish it, space wise when disks were small (4MB in size).

Today, the optimizer eliminates duplicates without eliminating dups because it is more efficient. The Inclusion merge join means simply that when the join step finds the first match, it stops looking for a secondary match for the table(s) referenced in the subquery. It would then move to the next row from the main table and compare it to the previous matching row from the subquery values. This is the difference between a merge join and an inclusion merge join - eliminates dups without actually doing a redistribution to eliminate dup values. Pretty ingenious!!

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023