 |
 |
Archives of the TeradataForum
Message Posted: Mon, 30 Jul 2007 @ 13:49:47 GMT
| Subj: | | Re: Finding rows in one table not in another |
| |
| From: | | Dieter Noeth |
Bob Duell wrote:
| | 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. | |
It not only looks better, it *is* better.
Usually there will be no real difference between IN/EXISTS/JOIN, but if there is then EXISTS is the most efficient.
| | No big deal; it's just a little Friday musing before I head off > to see the Simpson's movie! | |
It's better to ask before, after the movie you'll be Homerized and don't care any more :-)
Dieter
| |
