| David Portas 2005-04-27, 8:24 pm |
| It's called a Correlated Subquery. Theoretically you can assume that the
subquery is executed once for every row in the main (outer) part of the
statement. The correlation part is the reference to the outer table
(FooStringsImport.FooKey). In other words EXISTS returns true for any
particular row in FooStringsImport if there is at least one row in
FooStrings that matches FooStringsImport.FooKey.
This is a Selection operation, not a Join and a DELETE statement rather than
a query, so I don't see that it helps much to think in terms of a Cartesian
Product. In fact SQL Server may use join operations to execute statements
involving subqueries but the server is not going to join every row to every
row before deciding which rows to delete.
See also Joe Celko's narrative on how queries work:
http://www.google.co.uk/groups?selm...ooglegroups.com
By the way, NOT EXISTS is also possible. So rather than do a DELETE followed
by an INSERT why not just filter the INSERT statement in a similar way:
INSERT INTO FooStrings (...)
SELECT ...
FROM FooStringsImport
WHERE NOT EXISTS
(SELECT *
FROM FooStrings
WHERE FooStringsImport.FooKey = FooStrings.FooKey)
--
David Portas
SQL Server MVP
--
|