Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 11 Feb 2005 @ 21:35:07 GMT

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

Subj:   Re: Guidelines for using Distinct vs. group by
From:   Anantaraman, Kumaran (K.)

  Yes, that's what he's saying: all subqueries have an implicit distinct. If you think about it, it makes sense.  

That's not true.

A subquery itself can legitimately return one or more values.

[Mastering SQL by Martin Gruber] "When using subqueries in predicates based on relational operators (equations or inequalities), you must be sure to use a subquery that will produce one and only one row of output."

So in following,

       exp > (subquery_max_1_row_out)

Subquery1 must return zero, 1 row or NULL. If it returns more than 1 row, then at runtime, Teradata (or any DB) gives an error msg.

If we have the predicate

       exp IN (subquery_many_rows)

This subquery can return multiple rows.

Besides, there are 'subquery operators' that allow the subquery to return more rows too

     exp > ANY (subquery_many_rows)
     exp > ALL (subquery_many_rows)
     EXISTS (subquery_many_rows)

Kumaran Anantaraman

  <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