Archives of the TeradataForum
Message Posted: Fri, 27 Jul 2007 @ 21:58:47 GMT
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?
select a.* from ONE_BIGTABLE a left join ANOTHER_BIGTABLE b on a.key_field=b.key_field where b.key_field is null
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!
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|