Archives of the TeradataForum

Message Posted: Mon, 30 Jul 2007 @ 09:21:25 GMT

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

<-- Anonymously Posted: Monday, July 30, 2007 00:37 -->

Bob Duell wrote:

          > Method 1:

          >    select a.*
          >    from   ONE_BIGTABLE a
          >    left join ANOTHER_BIGTABLE b
          >       on a.key_field=b.key_field
          >    where b.key_field is null

          > Method 2:

          >    select a.*
          >    from   ONE_BIGTABLE a
          >    where not exists (
          >        select 1 from ANOTHER_BIGTABLE b
          >        where a.key_field=b.key_field)

Over time I've found that if I code it using your Method 2 (NOT EXISTS sub-select) then with a disturbing frequency the next request is of the type:

     WHERE
       the key isn't in the other table
       OR
       if it is in the other table then column b.x is in (purple, red).

And so I find myself having to rewrite the query back into Method 1 form anyway. This is especially common if the NOT EXISTS wasn't the only part of the WHERE clause.

Consequently these days I always write the code in Method 1 and only change it to Method 2 when it has become production code and I'm looking to make efficiency improvements.

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