Archives of the TeradataForum

Message Posted: Fri, 27 Jul 2007 @ 21:58:47 GMT

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

Hi,

This isn't a particularly Teradata question, but I'm going to ask anyway. It's probably just basic SQL.

I want to select all the rows from one table that do not exist in another table, based on comparing column values. In my case, the columns are a unique primary index to both tables (both varchar(255)).

Both of the following methods appear to be functionally equivalent. Is one "better" than the other?

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)

The second method "looks" better. Also, the first "explain" says two spools are created while the second says only one spool is created. But I'm wondering if this is a generally better technique rather than somehow better because the indexes are so similar.

No big deal; it's just a little Friday musing before I head off to see the Simpson's movie!

Thanks,

Bob

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