Posted by Erland Sommarskog on 10/01/77 11:19

rcamarda (rcamarda@cablespeed.com) writes:
> Simon,
> how is your sql different from
>
> insert into dbo.TableB
> (col1, col2, col3,...)
> select col1, col2, col3...
> from dbo.TableA a
> where keycol in (select keycol from dbo.tableb)

That's one hell of a difference - you are inserting the duplicates only. :-)

But, OK, put in the NOT, and your query is the same as Simon's. In SQL 6.5
there was a difference in performance, NOT IN usually executed slower. I
think that in SQL 2000, the optimizer rewrites the query internally.

Anyway, there is still an advantage with the style that Simon used.
Consider this query:

insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol1 = b.keycol1
and a.keycol2 = B.keycol2)

That is not easily recast to NOT IN.

So NOT EXISTS is simply an operation you need to master.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]