(np****@gmail.com) writes:
I want to pull back all users that did not have any outbound
shipments. I know I can do it with an inner select:
select user
from users
where user not in
(
select user
from shipments
where shipment = 'Outbound'
)
This seems so simple but I don't know how I'd do it without hitting
the database twice.
Who says that you hit it twice? Keep in mind that SQL is a declarative
language. You state your intent, the optimizer finds out the best way
do it. OK, so in practice it does matter quite a bit how your queries,
but there are also a lot of cases where the optimizer will implement
your query in a different way.
Which is the best way to express a query, is often difficult to tell
beforehand. When you have different ways to go, benchmarking is
about compulsory. But even then you need to be careful, because what
is true for 10000 rows and one distribution, may not be true for 10
million rows and another distribution.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Links for SQL Server Books Online:
SQL 2008:
http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:
http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx